Forum Discussion

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

Create Pie chart with group by

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  365...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
MiKeZZa
Post Patron
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      MiKeZZa
      Post Patron
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi MiKeZZa，

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

         

        Best Regards,
        Angelia