cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Count of Average by Group - Pie Chart

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
Regular Visitor

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?

Super User

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
Regular Visitor

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

Super User

Share the downoad link of the PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor
Super User

Those link take me to a sign-in page.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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 ?

Community Support

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

Frequent Visitor

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.

Super User

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?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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)

Frequent Visitor

Any other ideas?

Super User

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?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

#### Europe’s largest Microsoft Fabric Community Conference

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