specific value as denominator in table

[minseon]

Hi all

I am trying to calculate ration in table
table is like below

and i want to get result like this

i want to use the value of b for each year as the denominator

in dashboard i use year and month slicer
How would  write DAX to achieve the above result?

Thanks

[Rabi · Accepted solution]

Hi @minseon ,

This is exactly what you need:

Please follow the steps below:

1) Add a new column Denominator as below:

denominator = 
CALCULATE(
    SUM(DATA[value]),
    FILTER(
        DATA,
        DATA[category] = "b" && DATA[year] = EARLIER(DATA[year])
    )
)

 2) Then again add a new column as below:

pct = DATA[value]/DATA[denominator]

That is it your issue is now solved, Visualise the table using matrix or normal Table.

Please mark it correct if it has helped.

Rabi Karki

[minseon]

it works!!

Thanks RABI!!