Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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] )
Proud to be a Super User!
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] )
Proud to be a Super User!
That worked! Thanks danextian!
You're welcome!
Proud to be a Super User!
I may miss somthing
Total = SUMX(Table2,(Table2[1]+Table2[2]+Table2[3]+Table2[4]+Table2[5])/5)
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