Forum Discussion

umasirhc's avatar
umasirhc
Copper Contributor
Aug 13, 2020

Averaging Distinct Counts in a Pivot Table

OK, I've created a Pivot Table that pulls the distinct count of patient identifiers per date in one column by the date in the rows from a query.  I'm trying to get a calculation of the average daily ...
Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 15, 2020

    umasirhc 

    You may add measure as

    Daily Average of Unique Patients:=AVERAGEX(
   SUMMARIZE(
        Range,
        Range[DATE],
         "Daily Unique", DISTINCTCOUNT(Range[Patient ID])
     ),
[Daily Unique])

    Result is like

SergeiBaklan's avatar
SergeiBaklan
MVP
Aug 15, 2020

umasirhc 

You may add measure as

Daily Average of Unique Patients:=AVERAGEX(
   SUMMARIZE(
        Range,
        Range[DATE],
         "Daily Unique", DISTINCTCOUNT(Range[Patient ID])
     ),
[Daily Unique])

Result is like

  • umasirhc's avatar
    umasirhc
    Copper Contributor
    Aug 17, 2020

    SergeiBaklanThanks! This worked perfectly!

  • Frankie_Yim's avatar
    Frankie_Yim
    Copper Contributor
    Apr 19, 2022

    SergeiBaklan 

     

    hi sir, i also want to do this as above captured image, how to do?? i try to modify your completed excel but cant success...

     

Resources