cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
minapot Frequent Visitor
Frequent Visitor

how to show average as total for distinctcount measure

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

 

Capture.JPG

 

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

Re: how to show average as total for distinctcount measure

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.

 

averagex.png

 

 

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] )

 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
5 REPLIES 5
Super User
Super User

Re: how to show average as total for distinctcount measure

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jessica_Seiya Established Member
Established Member

Re: how to show average as total for distinctcount measure

I may miss somthing

 

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

2018-04-20_9-18-13.png

 

 

 

danextian New Contributor
New Contributor

Re: how to show average as total for distinctcount measure

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.

 

averagex.png

 

 

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] )

 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
minapot Frequent Visitor
Frequent Visitor

Re: how to show average as total for distinctcount measure

That worked! Thanks danextian!

danextian New Contributor
New Contributor

Re: how to show average as total for distinctcount measure

You're welcome!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa