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.

Reply
JoelTib
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.  

JoelTib_1-1699512162563.png

 

 

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

JoelTib_0-1699524731072.png

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

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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