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)

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
6 years ago

MrDarian 

 

please try below measure to create a column

ratio = 
VAR subamount=MAXX(FILTER(Sheet4,Sheet4[ID]=EARLIER(Sheet4[ID])&&Sheet4[SUBID]=EARLIER(Sheet4[SUBID])-1),Sheet4[Amount])
VAR subamount2=MAXX(FILTER(Sheet4,Sheet4[ID]=EARLIER(Sheet4[ID])-1&&Sheet4[SUBID]=max(Sheet4[SUBID])),Sheet4[Amount])
VAR minID=MIN(Sheet4[ID])
return if(Sheet4[ID]=minID,if(Sheet4[SUBID]=1,Sheet4[Amount]/Sheet4[Amount],Sheet4[Amount]/subamount),if(Sheet4[SUBID]<>1,Sheet4[Amount]/subamount,Sheet4[Amount]/subamount2))

 

 

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

thank you. is it possible to do by measure not calculated column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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)

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

      thanks for your response, measure also worked. is it possible to explain how to do?

      this is your measure.

      RatioMeasure =
      VAR _minsub =
      CALCULATE (
      MIN ( 'Table'[Sub ID] ),
      FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) )
      )
      VAR _maxsubid =
      CALCULATE (
      MIN ( 'Table'[Amount] ),
      FILTER (
      ALL ( 'Table' ),
      'Table'[ID]
      = MAX ( 'Table'[ID] ) - 1
      && 'Table'[Sub ID] = MAXX ( ALL ( 'Table' ), 'Table'[Sub ID] )
      )
      )
      VAR _previousamount =
      CALCULATE (
      MAX ( 'Table'[Amount] ),
      FILTER (
      ALL ( 'Table' ),
      'Table'[ID] = MAX ( 'Table'[ID] )
      && 'Table'[Sub ID]
      = MAX ( 'Table'[Sub ID] ) - 1
      )
      )
      RETURN
      SWITCH (
      TRUE (),
      MAX ( 'Table'[Sub ID] ) = _minsub
      && MAX ( 'Table'[ID] ) = MINX ( ALL ( 'Table'[ID] ), 'Table'[ID] ), DIVIDE ( MAX ( 'Table'[Amount] ), MAX ( 'Table'[Amount] ) ),
      MAX ( 'Table'[Sub ID] ) = _minsub, DIVIDE ( MAX ( 'Table'[Amount] ), _maxsubid ),
      DIVIDE ( MAX ( 'Table'[Amount] ), _previousamount ))