Forum Discussion

MrDarian's avatar
MrDarian
Icon for Helper II rankHelper II
6 years ago
Solved

Calculate Ratio

this is my table i want to calculate ratio based on ID and Sub ID in a measure. in power bi Like this:  
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi MrDarian ,

     

    Try this measure

     

    RatioMeasure =

    var _minsub = CALCULATE(MIN('Table22'[Sub ID ]), FILTER(ALL('Table22'),'Table22'[ID] = MAX('Table22'[ID])))
    var _maxsubid = CALCULATE(MIN('Table22'[Amount]),FILTER(ALL('Table22'),'Table22'[ID] = MAX('Table22'[ID])-1 && 'Table22'[Sub ID ] = MAXX(ALL('Table22'),Table22[Sub ID ])))
    var _previousamount = CALCULATE(MAX('Table22'[Amount]), FILTER(ALL('Table22'),'Table22'[ID] = MAX('Table22'[ID]) && 'Table22'[Sub ID ] = MAX( 'Table22'[Sub ID ]) -1))

    RETURN
    SWITCH(
    TRUE(),
     
    MAX('Table22'[Sub ID ]) = _minsub && MAX('Table22'[ID]) = MINX(ALL('Table22'[ID]),Table22[ID]), DIVIDE(MAX('Table22'[Amount]),MAX('Table22'[Amount])),
    MAX('Table22'[Sub ID ]) = _minsub, DIVIDE(MAX('Table22'[Amount]),_maxsubid),
    DIVIDE(MAX('Table22'[Amount]),_previousamount)
    )
     
     
     
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MrDarian ,

 

Create a Calculated Column

 

Column =

var _minsub = CALCULATE(MIN('Table'[Sub ID ]), FILTER('Table','Table'[ID] = EARLIER('Table'[ID])))
var _maxsubid = CALCULATE(MAX('Table'[Amount]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID])-1 && 'Table'[Sub ID ] = MAX('Table'[Sub ID ])))
var _previousamount = CALCULATE(MAX('Table'[Amount]), FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) && 'Table'[Sub ID ] = EARLIER( 'Table'[Sub ID ]) -1))

RETURN
SWITCH(
TRUE(),
'Table'[Sub ID ] = _minsub && 'Table'[ID] = MIN('Table'[ID]), DIVIDE('Table'[Amount],'Table'[Amount]),
'Table'[Sub ID ] = _minsub, DIVIDE('Table'[Amount],_maxsubid),
DIVIDE('Table'[Amount],_previousamount)
)
 
 
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)