Average only counting distinct values

[MickyG]

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

 

 

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.

[Icey]

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,

Icey

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

[lbendlin]

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.