Problem with pie chart measure

[Dunner2020]

Hi there,

I have created a measure that spits the area code if the area is contributing to 80% of total events otherwise it gives 'other' . Following is the meausre:

Pie_percent =

Var cal_percent = [cumulative event sum] /[total events]

return

IF(cal_percent <= 0.8, MAX('Table'[AreaCode]),"Others")

If I show this measure in a table visual, it looks like as follow:

When I try to drag the pie_percent measure into the legend field of the pie chart it does not drag. I also selected the pie chart visual, and checked the pie_percent measure, it went into the tooltip field. From there I tried to drag into the legend field but it did not work. Values field contains the [no of events] measure. Could anyone guide me on why is that happening?

[v-robertq-msft]

Hi, @Dunner2020 

According to your sample file, I suggest you use the calculated columns instead of measures, which can be placed into the pie chart and display the correct values, you can try my steps:

1. Create these calculated columns in ‘Table’:

No of Events =

CALCULATE(

            COUNTX(FILTER('Table 1',

                'Table 1'[Date]<=TODAY()),  

                'Table 1'[Section ID]))

Rank =

RANKX(ALLSELECTED('Table'),[No of Events],,DESC,Skip)

cumulative event sum =

CALCULATE(

    SUM('Table'[No of Events]),

    ALL('Table'),

'Table'[Rank]<=EARLIER('Table'[Rank]))

Pie_percent =

var _total=SUMX(ALL('Table'),[No of Events])

var _percentage= [cumulative event sum]/_total

return IF(_percentage<=0.8,[Area Code],"Others")

2. Create a pie chart and place fields, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunner2020]

@v-robertq-msft , sorry I renamed the tables. please check again

[v-robertq-msft]

Hi, @Dunner2020 

I think that Power BI does not support to use the measure to perform as legend or Axis of a chart, What’s more, pie chart is used to show the proportion of each category to the whole. According to your data, I can not figure out what’ your expected result clearly.

Would you like to post some sample data(without sensitive data) and your expected result? Thanks in advance.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunner2020]

@v-robertq-msft , Thanks for the reply. Sample file can be downloaded from here . Expected output is as follow:

The above output shows the area code of those area which contributed to 80% of the total events. Areas that contribute to remaining 20% of events are shown as 'Other category'. Hope that would make sense now.

[v-robertq-msft]

Hi, @Dunner2020 

Would you like to check the sample data you posted? I downloaded and opened the pbix file. But I can't find the ' Table' or anything linked to the problem you mentioned. Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunner2020]

@amitchandak , groups that measure creates are not fixed. I mean the groups names are area code. Some area contribute to 80% in some weeks but they may not contribute in next week. Not sure how to create independent table in that case

[amitchandak]

@Dunner2020 , This seems like a case of segmentation, You need to have this with an independent Table, You need to have area code table with others

refer https://www.youtube.com/watch?v=CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization