how to show average as total for distinctcount measure

[minapot]

Hi! I need help again on how to show the correct average total for a measure I created. In this example, Allentown needs to have 114 as total, ie, 118 +116 +115 +113 +110 = 572/5

Thanks for the help!

[danextian · Accepted solution]

HI @minapot

If I get it right, the values in columns 1-5 are the distinct count of employees and you want to show the average of these values in the total. Correct?  If so, use this formula: 

Average =
VAR AVERAGE_ =
    AVERAGEX ( ALL ( 'Table'[Number] ), [Distinct Count] )
RETURN
    ( IF ( HASONEVALUE ( 'Table'[Number] ), [Distinct Count], AVERAGE_ ) )

The AVERAGE_ variable takes the value of Distinct Count measure and divides the total by the number of unique values in the number column.  

Then in RETURN argument,  HASONEVALUE returns Disctinct Count measure when the context for Number column has been filtered down to one distinct value only. Since the Total in the matrix chart is not one distinct value, the AVERAGE_ variable is being returned.

Take note that if you put Number column in a slicer and select at least two values, the result will still be equal to 114.

If you want to show the average of just the selected values in  Number column change 

ALL ( 'Table'[Number] )

 to

ALLSELECTED ( 'Table'[Number] )

[minapot]

That worked! Thanks danextian!

[danextian]

You're welcome!

[Anonymous]

I may miss somthing

Total = SUMX(Table2,(Table2[1]+Table2[2]+Table2[3]+Table2[4]+Table2[5])/5)

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149