Forum Discussion
DAX - Proportion Calculation across multiple groupings
Dear Power BI Community,
I would like some help with the calculation of a proportion across multiple groupings.
Shown below is the table I am working on. I have Age Categories running from 15-19, 20-24, 25-29,... up to 85+. I then also have four K10 categories within each of these Age Categories. I would like to calculate the percentage of people in each K10 category, for each Age Category.
The '%' column is produced with a DAX measure, but it is not calculating what I need. The DAX measure I am using is as follows:
Any help with how I should amend this DAX measure to produce what I need, would be most welcome.
Kind regards,
Joel
2 Replies
JoelTib ,
I think you need to remove year , vertical
6.2 % K10 Cat =
DIVIDE(
COUNT(HILDA[xwaveid]),
CALCULATE(
COUNT(HILDA[xwaveid]),
Removefilters(HILDA[Year])
)
)For row wise sub total
6.2 % K10 Cat =
DIVIDE(
COUNT(HILDA[xwaveid]),
CALCULATE(
COUNT(HILDA[xwaveid]),
Removefilters(HILDA[K10 Category], HILDA[K10 - Sort],HILDA[Age Category], HILDA[Age Category - Sort])
)
)How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
Dear amitchandak,
Many thanks for your reply. Unfortunately, I don't think I was clear enough in my initial question.
I have mocked up the following table in Excel to detail exactly what I would like the DAX measure to produce.
You will see that I would like the proportion of each K10 category (Low, Moderate, High, Very High) as a function of the total for each Age Category.
Again, any assistance on how I can re-write my DAX expression to produce this would be wonderful.
Kind regards,
Joel
