Forum Discussion

ygangadhara's avatar
ygangadhara
New Member
3 years ago
Solved

Formulas two apply on Row Level

I'm hoping I can get a bit of help. I have data in two Rows need to calculate 3 rd row as mentioned below.

 

Value 1 & Value 2 are given 

Need to calculate Value3 = Value2/ Value1*100 for all the months.

 

 JanFeb
1. Value11012
2. Value223
3. Value32025

 

Any help would be greatly apprecaited. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ygangadhara 

    You can refer to the following example

    Sample data

    And I put the fields to the matrix visual, and create a measure as value to put in matrix

     

     

    Measure = var a=CALCULATE(SUM('Table'[Value]),OFFSET(-1,,ORDERBY('Table'[ValueType]),KEEP))
var b=CALCULATE(SUM('Table'[Value]),OFFSET(-2,,ORDERBY('Table'[ValueType]),KEEP))
return IF(SUM('Table'[Value])=BLANK(),DIVIDE(a,b)*100,SUM('Table'[Value]))

     

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ygangadhara 

    You can refer to the following example

    Sample data

    And I put the fields to the matrix visual, and create a measure as value to put in matrix

     

     

    Measure = var a=CALCULATE(SUM('Table'[Value]),OFFSET(-1,,ORDERBY('Table'[ValueType]),KEEP))
var b=CALCULATE(SUM('Table'[Value]),OFFSET(-2,,ORDERBY('Table'[ValueType]),KEEP))
return IF(SUM('Table'[Value])=BLANK(),DIVIDE(a,b)*100,SUM('Table'[Value]))

     

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.