DAX - Proportion Calculation across multiple groupings

[JoelTib]

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

[JoelTib]

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

[amitchandak]

@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