Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Average only counting distinct values

How much would you charge to adjust 1 measure in a table matrix? Here is the table. The column I’m having issues with is “County Cases per 100k” (pic 1).





The measure that calculates this column is “per100kWeekend” (pic 1.1). It returns the value of the “countyper100k” measure that is equal to the Weekend date column.




Pic 2 shows the "Countyper100k" measure. It summarizes the county data table.



Pic 3 shows the "County COVID Data" table. The column I need is the "cases_avg_per_100k".


Pic 3.jpg


The problem I'm having...if you compare the first screenshot to Pic 4...when the regions are expanded. If you calculate the values they should average to 43.25 but when it is collapsed it shows 54. I'm guessing it is only averaging distinct values. How can I fix this? 






Table Structure below.



Community Support
Community Support

Hi @MickyG ,


Power BI won't calculate the total vaule based on the single values above. In other words, when the regions are expanded, the value of "Austin" isn't calculated based on the values of "Bluff Springs", "Health Professions", and etc.. It is calculated based on the overall max date of "Austin".


For Bluff Springs, per100kWeekend calculate [countyper100k] based on the max date of Bluff Springs.


For Austin, per100kWeekend calculate [countyper100k] based on the max date of Austin.


If I have not made it clear, please tell me your doubts.


Here is an example: Measure Totals, The Final Word - Microsoft Power BI Community



Best Regards,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User
Super User

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors