# Averaging Distinct Counts in a Pivot Table

Occasional Contributor

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

6 Replies

# Re: Averaging Distinct Counts in a Pivot Table

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

# Re: Averaging Distinct Counts in a Pivot Table

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

# Re: Averaging Distinct Counts in a Pivot Table

use unique and countif

it should work

here you go

# Re: Averaging Distinct Counts in a Pivot Table

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

Result is like

# Re: Averaging Distinct Counts in a Pivot Table

@Sergei BaklanThanks! This worked perfectly!

# Re: Averaging Distinct Counts in a Pivot Table

@umasirhc , you are welcome