Count of Average by Group - Pie Chart

[jshaw]

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)

[Dgo1973]

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?

[Ashish_Mathur]

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.

[Dgo1973]

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

        )

    )

[Ashish_Mathur]

Share the downoad link of the PBI file.

[Dgo1973]

https://app.powerbi.com/groups/me/reports/b411eccd-8c09-40c2-ac30-e28a6df0af51

https://app.powerbi.com/groups/me/datasets/c245e6ee-794a-47fb-8b25-98fca71cd963/details

[Ashish_Mathur]

Those link take me to a sign-in page.

[Dgo1973]

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 ?

[v-juanli-msft]

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

[jshaw]

@v-juanli-msft @Greg_Deckler

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. 

[Greg_Deckler]

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?

[jshaw]

@Greg_Deckler @v-juanli-msft

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)

[jshaw]

Any other ideas?

[Greg_Deckler]

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?

[jshaw]

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!