Forum Discussion

gcam032's avatar
gcam032
Icon for Helper I rankHelper I
6 years ago
Solved

Calculate within group % over xaxis

Hi All,   Haven't found the exact answer in the forums that seems to work for what I need. I have a chart (see image) where the xaxis is a categorical variable (0, 100, 200, 300, 400.... 1000) gro...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi gcam032 ，

     

    1.Create a dim table:

     

     

     

    dimdate = ADDCOLUMNS(CALENDARAUTO(),"YEARMONTH",YEAR([Date])*100+MONTH([Date]))

     

     

     

    2.Create a measure：

     

     

     

    Measure = CALCULATE(SUM('Table'[value])) / CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Category]))

     

     

     

    Finally you will see:

     

     

    For the related .pbxi file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

     
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi gcam032 ，

 

1.Create a dim table:

 

 

 

dimdate = ADDCOLUMNS(CALENDARAUTO(),"YEARMONTH",YEAR([Date])*100+MONTH([Date]))

 

 

 

2.Create a measure：

 

 

 

Measure = CALCULATE(SUM('Table'[value])) / CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Category]))

 

 

 

Finally you will see:

 

 

For the related .pbxi file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 
  • gcam032's avatar
    gcam032
    Icon for Helper I rankHelper I
    6 years ago

    Hi Kelly,

     

    Thanks for providing your solution.  It's close, but I need a slightly different calculation - can you help?

     

    In your example, considering the Purple one, the percentages should be:

    11% (11)

    56% (56)

    34% (34)

    Sum to 100%

     

    Pink would be:

    13% (8)

    53% (34)

    34% (22)

    Sum to 100%

     

    Does that make sense?  It's within-group densities, where a group = YEARMONTH

     

    • gcam032's avatar
      gcam032
      Icon for Helper I rankHelper I
      6 years ago

      After some trial and error- I figured out that I had to include the other filters I had applied to the chart.  Your solution was spot on!  Thank you!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      Share the link from where i can download your PBI file.