Create pie chart based on measure

[cheryl0316]

This is a sample report regarding employee performance.

• If an employee doesn't meet the sales target, he will receive a notice.
• If an employee exceeds the maximum allowed refund amount, he will receive a notice.
• If an employee doesn't meet both requirements, he will receive two notices.

The measure "Number_Notice" calculates the number of notices

I want to calculate the number of notices based on each country, city, employee, and product category.

The measure works well in the table.

I created a table for the slicer "Number of Notice". If the results returned by the measure "Number_Notice" match the slicer selections, they will be included.

e.g. I select Flag:1

I want to create a pie chart in PowerBI to show the distribution of notices. This is an example created in Excel.

I think there are 2 key points.

1. The calculation of the number of notices based on each country, city, employee, and product category works well in the table as long as I include the respective columns, but I'm not sure how to calculate it in a pie chart.

2. The pie chart needs to interact with the slicers. I'm not sure how to filter the pie chart based on the slicer selections for 'Number of Notices,' as it doesn’t have relationships with other tables.

Any advice would be appreciated. Thank you so much!

https://drive.google.com/file/d/1w_C86cENjnTki4k2gazHVTNyT1aa3Nac/view?usp=sharing 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[lbendlin · Accepted solution]

Power BI  (unlike Tableau) does not support dynamic bucketing.  You need to bring your own buckets, in a disconnected table - like you did in the "Notice"  table. You can then base your horrible pie chart on that table.

[cheryl0316]

Thank you for your reply!

Just one question: Why can't we apply [Sum_Sales Amount] > 0 directly in 'Filters on this visual'?

[lbendlin]

you need to group first before you can apply the measure filter. Otherwise it would attempt to filter the individual rows.

[cheryl0316]

One more question:

How can I create a pie chart that shows the count for each notice type?

I want the pie chart to show:

• Number of Sales Notices
• Number of Refund Notices

Do I need to create a separate measure for each notice type?

[lbendlin]

You can change your buckets to represent notice types. (Sales or Refund)  Then adjust your measure accordingly

Are you sure a pie chart is the right chart for the data story you are trying to tell?

[cheryl0316]

Yes I know that's a bit weird

I want to create a pie chart in PowerBI to show the distribution of each notice type. This is an example created in Excel

I want to calculate the number of each notice type based on each country, city, employee, and product category.

The number of highlighted cells is same as the values shown for each notice type in the pie chart above

My current thought:

1. Create a new table with a column called "Notice Type," which will be used as the Legend in the pie chart.

2. Create a measure

NoticeTypeCount =

VAR Sales_Notice = IF([Sum_Sales Amount] < 100, 1, 0)

VAR Refund_Notice = IF([Sum_Refund Amount] > 10, 1, 0)

VAR a = SUMMARIZECOLUMNS(dimLocation[Country],dimLocation[City],dimEmployee[Employee Name],dimProduct[Product Category])

VAR b = ADDCOLUMNS(a, "Sales notice", Sales_Notice, "Refund notice", Refund_Notice, "Revenue amount", [Sum_Sales Amount])

return countrows(filter(b,[Revenue amount]>0))

I think var b is wrong because "Sales notice" and "Refund notice" should be grouped under a column (?)

I’m a bit confused when it comes to summarizing tables or columns. Any advice would be appreciated. Thank you for your help!