cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gcam032
Helper I
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 %.

 

Screenshot 2020-03-30 at 16.44.27.png

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
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:

 

Annotation 2020-03-31 152128.png

 

For the related .pbxi file,pls click here.

 

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

 

 

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
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:

 

Annotation 2020-03-31 152128.png

 

For the related .pbxi file,pls click here.

 

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

 

 

View solution in original post

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

 

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!

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User IV
Super User IV

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors