- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- how to show average as total for distinctcount mea...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

minapot

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
05:38 PM

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.

1 ACCEPTED SOLUTION

Accepted Solutions

danextian

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
06:28 PM

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

www.linkedin.com/in/danebelarminocpa

www.linkedin.com/in/danebelarminocpa

5 REPLIES 5

Greg_Deckler

Super User

Re: how to show average as total for distinctcount measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
06:08 PM

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

Proud to be a Datanaut!

Jessica_Seiya

Established Member

Re: how to show average as total for distinctcount measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
06:18 PM

I may miss somthing

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

danextian

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
06:28 PM

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

www.linkedin.com/in/danebelarminocpa

www.linkedin.com/in/danebelarminocpa

minapot

Frequent Visitor

Re: how to show average as total for distinctcount measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
08:59 PM

That worked! Thanks danextian!

danextian

New Contributor

Re: how to show average as total for distinctcount measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
09:16 PM

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

www.linkedin.com/in/danebelarminocpa