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

 

Need Help

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

     

     

  • Sean's avatar
    Sean
    Community Champion
    9 years ago
    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?

     

  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    9 years ago

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


         

    • MiKeZZa's avatar
      MiKeZZa
      Post Patron
      9 years ago

      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: