Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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:

6.2 % K10 Cat =
DIVIDE(
COUNT(HILDA[xwaveid]),
CALCULATE(
COUNT(HILDA[xwaveid]),
ALL(HILDA[K10 Category], HILDA[K10 - Sort]),
ALL(HILDA[Age Category], HILDA[Age Category - Sort])
)
)

Any help with how I should amend this DAX measure to produce what I need, would be most welcome.

Kind regards,

Joel

2 REPLIES 2
Frequent Visitor

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

Super User

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors