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!

     

     
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!