cancel
Showing results for
Did you mean:
Helper I

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

1 ACCEPTED SOLUTION
Community Support

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:

Best Regards,
Kelly

5 REPLIES 5
Community Support

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:

Best Regards,
Kelly

Helper I

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

Helper I

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!

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements