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

Ramiz_Assaf's avatar
Ramiz_Assaf
Iron Contributor
Aug 14, 2020

umasirhc 

can you please upload an example file explaining on the file what you need?

umasirhc's avatar
umasirhc
Copper Contributor
Aug 14, 2020

Ramiz_AssafWell I thought I responded earlier but it didn't go through.  I added an example sheet to the original post.  I used a pivot table and the data model to get the distinct count by patient ID, but then I want to get the daily average of distinct patient IDs.  I know I can just plug the grand total cells into an average formula but I'd like it to be a little more automated since the data table will just continue to grow.

Resources