Create Pie chart with group by

[MiKeZZa]

Hi all,

I have a dataset like this:

emp nr  year_month uniquenumber
161860 201702         25005
161860 201702         25006 
161860 201703         25007 
365630 201702         25008 
365630 201703         25009 

And then I don't want to do some tricks in SQL, but in DAX to create a Pie Chart with 2 parts:

1 part with 3 (for emp nr 161860)

1 part with 2 (for emp nr 365630)

Is there anybody who can tell me how to fix this? I've been struggling with summarize and can't make it unfortionatelly.

[MiKeZZa]

Out of the box is even better.... 🙂

Both answers are not exactly what I'm looking for. To be more clear some extra data:

emp nr  year_month uniquenumber
161860 201702         25005
161860 201702         25006 
161860 201703         25007 
365630 201702         25008 
365630 201703         25009 

365631 201702         25010 
365631 201703         25011 

365632 201702         25012 
365632 201703         25013 

1 part with 3 (for emp nr 161860)

3 part's with 2 (for emp nr 365630, 365631 and 365632).

So I want 2 parts; 1 with 25% and value 3 and 1 with 75% and value 2. Tomorrow I'll check the link; maybe that explains everything...

[v-huizhn-msft]

Hi @MiKeZZa,

First, you should create a calculated column to get times each employee recorded using the formula.

times = CALCULATE(COUNTA(Test[emp nr]),ALLEXCEPT(Test,Test[emp nr]))

>>So I want 2 parts; 1 with 25% and value 3 and 1 with 75% and value 2. Tomorrow I'll check the link; maybe that explains everything...

Create a measure to calculted the percentage. 

Percent = CALCULATE(COUNTAX(DISTINCT(SELECTCOLUMNS(Test,"emp nr",Test[emp nr],"num",Test[times])),Test[Measure]),ALLEXCEPT(Test,Test[times]))/CALCULATE(COUNTAX(DISTINCT(SELECTCOLUMNS(Test,"emp nr",Test[emp nr],"num",Test[times])),Test[Measure]),ALL(Test))

Finally, create a pie chart, select the times as Legend level, the Percent as value level, please see the expected result in screenshot below.



Please let me know if you have any question.

Best Regards,
Angelia

[MiKeZZa]

Hi @v-huizhn-msft, thank you for this good tutorial. Problem is that I forget 1 part of the problem... I haven't been complete and my dataset is different now.... My complete dataset looks this way now:

emp nr  year_month numberoftimestocount costcentre
161860 201702         2                                    1234
161860 201703         1                                    1234
365630 201702         1                                    1234
365630 201703         0                                    1234

365630 201702         1                                    2345
365630 201703         0                                    2345

365631 201702         0                                    1234
365631 201703         1                                    1234

365632 201702         0                                    1234  
365632 201703         0                                    1234

Based on this dataset I want to see this:
161860 is 3 times sick in total dataset
365630 is 1 time sick in total dataset (despite of that the sum is 2, because of that it is the same month, but splitted into 2 records because of costcentre)

365631 is 1 time sick in total dataset
365632 is 0 times sick in total dataset

So my graph must then be:

[v-huizhn-msft]

Hi @MiKeZZa,

Plesae add another filter in times calculated columns. You will get correct result.

times = CALCULATE(SUM(Table3[number]),ALLEXCEPT(Table3,Table3[emp nr],Table3[costcentre]))

Then create measure like the solution above.



Best Regards,
Angelia

[MiKeZZa]

Hi @v-huizhn-msft thanks for you're patience.... But something goes wrong. I now have:

numberofcategories = CALCULATE(SUM('table name'[empnr]),ALLEXCEPT('table name','table name'[empnr],'table name'[costcenter]))

incidentcount = sum('table name'[numberofincidentsinmonth])

percentage = CALCULATE(COUNTAX(DISTINCT(SELECTCOLUMNS('table name',"empnr",'table name'[empnr],"num",'table name'[numberofcategories])),'table name'[incidentcount]),ALLEXCEPT('table name','table name'[numberofcategories]))/CALCULATE(COUNTAX(DISTINCT(SELECTCOLUMNS('table name',"empnr",'table name'[empnr],"num",'table name'[numberofcategories])),'table name'[incidentcount]),ALL('table name'))

And this results in this piechart:

I'm pretty sure I've done everything that you suggest, but it's clear that something is wrong.

[v-huizhn-msft]

Hi @MiKeZZa，

In this chart, it seems that you post your table[emp nr ] as Lengend, please post table[numberofcategories] as lengend. And do you create measure your calculated column?

Best Regards,
Angelia

[MiKeZZa]

Hi Angelia,

I really don't understand you're questions. What do you mean with Lengend? And 'do you create measure your calculated column'?

Sorry, I'm really desperate for help, but don't understand what you're asking...

[v-huizhn-msft]

Hi @MiKeZZa，

For pie visual, you select the "numberofcategories" field as Legend level, and Percent measure as value level?

Best Regards,
Angelia

[MiKeZZa]

No, still not good unfortionately....

If I select 1 person I have this dataset:

emp nr  year_month numberoftimestocount costcentre

161860 201606         0                                    1234

161860 201607         0                                    1234

161860 201608         0                                    1234

161860 201609         0                                    1234

161860 201610         0                                    1234

161860 201611         0                                    1234

161860 201612         0                                    1234

161860 201701         1                                    1234

161860 201702         0                                    1234
161860 201703         0                                    1234

This must result in 1 person that has 1 as count. So it must be a piechart with 100% score for group '1'. But what I get is this:

So it looks like he splits the data on the number of incidents, instead of summing them....

Can somebody PLEASE help me?

[MiKeZZa]

I've spent a few hours this weekend but it's really hard.... Anyone with more DAX skills than me?

[v-huizhn-msft]

Hi @MiKeZZa,

Could you please share your .pbix for further analysis?

Best Regards,
Angelia

[MiKeZZa]

Here is the (anonymised) pbix file: Download

It's (in my opinion) all about the fields used in the Pie Chart.

The filtered person has 2 incidents so I expect a pie with value 2 filled for 100%.

[MiKeZZa]

Anyone an idea?

[MiKeZZa]

I have tried it with the blog but I don't think it's what I need. I don't want to make my own groups, but I want to group on the outcome of the calculation. So it must become something like this:

 

[Sean]

---

@MiKeZZa wrote:

 in DAX to create a Pie Chart with 2 parts:

1 part with 3 (for emp nr 161860)

1 part with 2 (for emp nr 365630)

---

I'm not sure - is this what you are after?

[Seth_C_Bauer]

@MiKeZZa Not a direct solution as you are requesting DAX, but you can do this with Out of the Box functionality in Power BI without DAX.

Check out this blog

Just threw this together off your request. 2 minutes.