SOLVED

Averaging Distinct Counts in a Pivot Table

Occasional Contributor

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 number of distinct patient identifiers but I'm struggling.  I've added the data to the Data Model and tried to play around with measures, but just can't get it to work.  Any ideas?  How do you get Excel to calculate a distinct count for each day and then average those distinct counts by the number of dates in the Pivot Table?  Sorry I can't provide a test sheet with this question.

 

9 Replies

@umasirhc 

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

@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.

@umasirhc 

 

use unique and countif

it should work 

here you go

best response confirmed by Hans Vogelaar (MVP)
Solution

@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

image.png

@Sergei BaklanThanks! This worked perfectly!

@umasirhc , you are welcome

@Sergei Baklan 

 

Frankie_Yim_0-1650334733202.png

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...

 

@Frankie_Yim 

 

Frankie_Yim_0-1650346771749.png

 

 

i solved~~many thanks

@Ramiz_Assaf 

Hi, I'm trying to do something similar. I've Purchase Order numbers, split per line. I'm looking to get the average order value per PO. It's easy to get the average value per line, but what I need is the average value per distinct PO number. I've attached an excel as an example, but my dataset is much larger. In the example the average value is 288 for everything but I need to know the average of each PO. The value I need is £396.10 which is the total for each PO number divided by the amount of PO's placed (8 in this example).

PO NumberPO Line NumberPO Value GBP Equiv
O033314001193.5
O03331400250.08
O03331500113.23
O03331500234.79
O03331600111.11
O03331700195
O0333180011465
O033319001248.2
O033320001100
O033321001954.78
O0333210023.13