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
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

      • MiKeZZa's avatar
        MiKeZZa
        Post Patron
        9 years ago

        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?

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

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

        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...