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

My value is a sum.

I then have a Legend of YearMonth from a Date Dim table.

 

What I need is to display a %.  Each YearMonth % should add to 100% distributed across the categorical values.  In other words, a distribution density for each YearMonth.  So the raw figures below will be normalised as a density and displayed as %.

 

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!

     

     

5 Replies

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