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 dashboar...
  • 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

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

  • minseon's avatar
    minseon
    Frequent Visitor
    2 years ago

    it works!!

    Thanks RABI!!