The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Count of Average by Group - Pie Chart

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Count of Average by Group - Pie Chart

07-18-2018
07:36 AM

I am trying to make a piechart with the following data.

ID | Subject | Group | Sub Group | Rating |

1 | Math | A | A-1 | 1 |

2 | Math | A | A-1 | 2 |

3 | Math | A | A-1 | 3 |

4 | Math | A | A-1 | 1 |

2 | Science | B | B-1 | 4 |

4 | Science | B | B-1 | 1 |

3 | Science | B | B-1 | 3 |

1 | Science | B | B-1 | 3 |

4 | Reading | A | A-2 | 2 |

1 | Reading | A | A-2 | 2 |

I want to take the average rating for each unique ID, and then the display the count of the average for each ID, in a pie chart, grouped by rating.

So essentially, I want to show how many IDs have an average of each rating. (So my pie chart would have 4 sections, and the values would add up to the distinct number of ID). However, I also need to be able to filter by subject, then by group, and then by sub group.

**EDIT: **

Here is what I have so far.

Here is the data set I am using.

ID | Subject | Group | Sub Group | Rating |

1 | Math | A | A-1 | 1 |

2 | Math | A | A-1 | 2 |

3 | Math | A | A-1 | 3 |

4 | Math | A | A-1 | 1 |

5 | Math | A | A-1 | 4 |

6 | Math | A | A-1 | 3 |

2 | Science | B | B-1 | 4 |

4 | Science | B | B-1 | 1 |

3 | Science | B | B-1 | 3 |

1 | Science | B | B-1 | 4 |

5 | Science | B | B-1 | 2 |

6 | Science | B | B-1 | 1 |

4 | Reading | A | A-2 | 2 |

1 | Reading | A | A-2 | 2 |

2 | Reading | A | A-2 | 3 |

3 | Reading | A | A-2 | 4 |

5 | Reading | A | A-2 | 1 |

6 | Reading | A | A-2 | 2 |

2 | Math | L | L-1 | 1 |

3 | Math | L | L-1 | 3 |

1 | Math | L | L-1 | 4 |

4 | Math | L | L-1 | 2 |

5 | Math | L | L-1 | 4 |

6 | Math | L | L-1 | 3 |

This is my measure I am using to calculate the average of Rating, based on the ID column.

Average of Rating average per ID = AVERAGEX( VALUES('Sheet1'[ID]), CALCULATE(AVERAGE('Sheet1'[Rating])) )

This is the measure I am using to Count, each ID based off their average rating. (I have 4 of these measures, one for each rating)

CountOf1 = CALCULATE(DISTINCTCOUNT(Sheet1[ID]), FILTER(Sheet1,[Average of Rating average per ID] = 1))

Everything works as expected, except for the Pie chart. The pie chart is showing the count of everything, and I need it to be grouped by unique IDs. So in the Pie chart, I would expect the "CountOf3" section to show a value of 4. And I would expect the "CountOf2" section, to show the value of 2. ("CountOf1" and "CountOf4" sections would be 0, and not show in the pie chart)

14 REPLIES 14

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-28-2023
03:49 PM

I am having a similar issue.

I have rows of data with %'s.

I want to group the rows of data by Divsion on a pie chart and have set up the pie chart to do a wighted avaerge of the %. but insted i get this:

There has to be a setting on the pie chart that i am missing, that is preventing me from getting the results I want

here's with weighted average, similar result 🙄

Sum of Standard Hours Efficiency weighted by Average of Standard Hours Efficiency per DIVI =

VAR __CATEGORY_VALUES = VALUES('DBS WOPHDRS0'[DIVI])

RETURN

DIVIDE(

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(

SUM('DBS WOPSEGS0'[Standard Hours Efficiency])

* AVERAGE('DBS WOPSEGS0'[Standard Hours Efficiency])

)

),

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(AVERAGE('DBS WOPSEGS0'[Standard Hours Efficiency]))

)

)

help?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-28-2023
04:24 PM

Hi,

Share some data, explain the question and show the expected result in a simple Table format. From there we canbuid any viz we want.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-28-2023
04:34 PM

so here is the data for one of the 4 Divisions, ( Division G)

As you can see at the bottom right of the table the average calculation is displaying 40.87%

The Pie chart should show 40.87% for Division G, but instead shows 8.25 million 😖

in fact every division is calculated at the same average of 8.25M

which makes no sense

this is the formula behind the pie chart, need to find how to correct it

Sum of Standard Hours Efficiency weighted by Average of Standard Hours Efficiency per DIVI weighted by Average of Standard Hours Efficiency per DIVI =

VAR __CATEGORY_VALUES = VALUES('DBS WOPHDRS0'[DIVI])

RETURN

DIVIDE(

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(

[Sum of Standard Hours Efficiency weighted by Average of Standard Hours Efficiency per DIVI]

* AVERAGE('DBS WOPSEGS0'[Standard Hours Efficiency])

)

),

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(AVERAGE('DBS WOPSEGS0'[Standard Hours Efficiency]))

)

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-28-2023
05:30 PM

Share the downoad link of the PBI file.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-01-2023
04:20 PM

Those link take me to a sign-in page.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-01-2023
04:22 PM

That's the only link I know to share.

What other way there is to share a link when you have comapny license power bi ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2018
02:38 AM

Hi @jshaw

*I want to take the average rating for each unique ID, and then the display the count of the average for each ID, in a pie chart, grouped by rating.*

To get *average rating for each unique ID, *create a measure below

average rating for each unique ID = CALCULATE(AVERAGE(Sheet7[Rating]),ALLEXCEPT(Sheet7,Sheet7[ID]))

I wonder whether *the average rating for each unique ID is the same with he count of the average for each ID, *and if it is, when i add *the average rating for each unique ID *to a pie chart and group by rating, it shows as below

*show how many IDs have an average of each rating, So my pie chart would have 4 sections, and the values would add up to the distinct number of ID*

Based on my understanding, it is just like below, is my understanding right?

distinct number of ID per rating = CALCULATE(DISTINCTCOUNT(Sheet7[ID]),ALLEXCEPT(Sheet7,Sheet7[Rating]))

Then you are able to filter by subject, then by group, and then by sub group.

Best Regards

Maggie

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-22-2018
06:21 PM

This solution doesnt seem to be correct. The pie chart, needs to show the distinct count of each ID, based off of the average rating for each ID.

This is the measure I am using to get the Average Rating for each ID.

Average of Rating average per ID =

AVERAGEX(

VALUES('Sheet1'[ID]),

CALCULATE(AVERAGE('Sheet1'[Rating]))

)

But when I put it in a pie chart, it ends up not working correctly. It shows 10% for ratign 1, 20% for rating 2, 30% for rating 3, and 40% for rating 4.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2018
05:20 AM

Is that with the data that you presented originally? Can you post a screen shot or tell us what columns and measures you have in which fields in your pie chart?

Vote for my sticker!

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-27-2018
07:06 AM

Here is a screenshot of what I have so far.

Here is the data set I am using.

ID | Subject | Group | Sub Group | Rating |

1 | Math | A | A-1 | 1 |

2 | Math | A | A-1 | 2 |

3 | Math | A | A-1 | 3 |

4 | Math | A | A-1 | 1 |

5 | Math | A | A-1 | 4 |

6 | Math | A | A-1 | 3 |

2 | Science | B | B-1 | 4 |

4 | Science | B | B-1 | 1 |

3 | Science | B | B-1 | 3 |

1 | Science | B | B-1 | 4 |

5 | Science | B | B-1 | 2 |

6 | Science | B | B-1 | 1 |

4 | Reading | A | A-2 | 2 |

1 | Reading | A | A-2 | 2 |

2 | Reading | A | A-2 | 3 |

3 | Reading | A | A-2 | 4 |

5 | Reading | A | A-2 | 1 |

6 | Reading | A | A-2 | 2 |

2 | Math | L | L-1 | 1 |

3 | Math | L | L-1 | 3 |

1 | Math | L | L-1 | 4 |

4 | Math | L | L-1 | 2 |

5 | Math | L | L-1 | 4 |

6 | Math | L | L-1 | 3 |

This is my measure I am using to calculate the average of Rating, based on the ID column.

Average of Rating average per ID = AVERAGEX( VALUES('Sheet1'[ID]), CALCULATE(AVERAGE('Sheet1'[Rating])) )

This is the measure I am using to Count, each ID based off their average rating. (I have 4 of these measures, one for each rating)

CountOf1 = CALCULATE(DISTINCTCOUNT(Sheet1[ID]), FILTER(Sheet1,[Average of Rating average per ID] = 1))

Everything works as expected, except for the Pie chart. The pie chart is showing the count of everything, and I need it to be grouped by unique IDs. So in the Pie chart, I would expect the "CountOf3" section to show a value of 4. And I would expect the "CountOf2" section, to show the value of 2. ("CountOf1" and "CountOf4" sections would be 0, and not show in the pie chart)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-08-2018
08:09 AM

Any other ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-18-2018
09:09 AM

I don't think that I am entirely understanding what you are wanting to do here. What would your expected results be from the data given? Computing the average for each ID I get:

1 - 2.00

2 - 3.00

3 - 3.00

4 - 1.33

So, would you expect a pie chart where 2 would have a count of 1, 3 would have a count of 2 and 1.33 has a count of 1?

Vote for my sticker!

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-18-2018
03:26 PM

My apologies for the lack of clarity, it is a little confusing. But yes, I beleive what you described is correct. However, the only condition is that when I filter, I want the calculated average to change based off the filter. So for example, if I filtered by math as the subject, then I would want the averages to re-compute based off only the data with the subject as math. And then I would still expect that all the 4 sections ofthe pie chart would add up to the number of distinct IDs.

Hopefully that makes sense!

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Check out the July 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

125 | |

116 | |

86 | |

48 | |

47 |

Top Kudoed Authors

User | Count |
---|---|

190 | |

111 | |

84 | |

67 | |

66 |