Calculate within group % over xaxis

[gcam032]

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

[v-kelly-msft · Accepted solution]

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]

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]

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]

Hi,

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

[Greg_Deckler]

Divide by 100? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490