Forum Discussion

minseon's avatar
minseon
Frequent Visitor
2 years ago
Solved

specific value as denominator in table

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's avatar
    Rabi
    2 years ago

    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
    LinkedIn

2 Replies

  • Rabi's avatar
    Rabi
    Icon for Resolver I rankResolver I
    2 years ago

    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
    LinkedIn