cancel
Showing results for
Did you mean:
Highlighted
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

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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."
5 REPLIES 5
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!

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

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.

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

## Re: how to show average as total for distinctcount measure

That worked! Thanks danextian!

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