Forum Discussion

tod's avatar
tod
Frequent Visitor
5 years ago
Solved

Calculation between rows with conditions

Hi, I'm trying to create a custom column showing % from data between rows. Best way to show what I'm trying to do is the data set here: Column A Column B Column C Custom Column D 1 A 20 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi tod  ,  

     

    You could create a measure by the following formula: 

    Custom Column D =
MAX ( [Column C] )
    / CALCULATE (
        MAX ( 'Table'[Column C] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Column A] = MAX ( 'Table'[Column A] ) ),
        'Table'[Column B] = "B"
    )

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   

tod's avatar
tod
Frequent Visitor
5 years ago

Thanks Pat. Using your formula, Columns A and C are summing as shown. They should remain in their original format. None of the columns are "summarized"; they are "Don't summarize".

This is how I interpreted your formula:

 
Custom Custom =
VAR thisvalue = 'Table'[Column C]
VAR thisAvalue = 'Table'[Column A]
VAR thisAvalueB =
AVERAGEX (
FILTER ( 'Table', 'Table'[Column A] = thisAvalue && 'Table'[Column B] = "B" ),
'Table'[Column C]
)
RETURN
DIVIDE ( thisvalue, thisAvalueB )
 
Did I do this right?