cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leo_89
Post Prodigy
Post Prodigy

Problem with pie chart measure

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:

 
 

Screenshot 2020-11-04 155205.jpg

 

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?

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

Hi, @leo_89 

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")
  1. Create a pie chart and place fields, like this:

v-robertq-msft_0-1604986795726.png

 

 

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.

leo_89
Post Prodigy
Post Prodigy

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

v-robertq-msft
Community Support
Community Support

Hi, @leo_89 

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.

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

 

leo_89_0-1604806735954.png

 

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.

Hi, @leo_89 

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.

leo_89
Post Prodigy
Post Prodigy

@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
Super User IV
Super User IV

@leo_89 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors