Forum Discussion

MrDarian's avatar
MrDarian
Helper 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)

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi MrDarian ,

     

    It seems that you always use the current column value divide by the previous column value no matter the ID and SUB ID, right?

     

    Aiolos Zhao

    • MrDarian's avatar
      MrDarian
      Helper II
      6 years ago

      ID and sub ID are important because ID is changed and sub ID start from 1 to 4

      i need a dax code to divide rows.

    • MrDarian's avatar
      MrDarian
      Helper II
      6 years ago

      ID is changed and sub ID start from 1 to 4

      i need a dax code to divide rows based on the previous row ( the logic previous sub ID and when ID is changed sub ID starts from 1. )

      then both of them are necessary.

  • ryan_mayu's avatar
    ryan_mayu
    Super 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
      Helper 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)

  • 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)

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    MrDarian 

     

    I have improved the DAX beause I am not if the max of subid is always 4 for each ID.

    The previous DAX has some problem .

     

    Please try the below measure

    RATIO = 
VAR ID2=if('Sheet4 (2)'[ID]=min('Sheet4 (2)'[ID])&&'Sheet4 (2)'[SUBID]=1,'Sheet4 (2)'[ID],if('Sheet4 (2)'[SUBID]=1,'Sheet4 (2)'[ID]-1,'Sheet4 (2)'[ID]))
VAR SUBID2=IF('Sheet4 (2)'[ID]=MIN('Sheet4 (2)'[ID])&&'Sheet4 (2)'[SUBID]=1,1,IF('Sheet4 (2)'[SUBID]=1,MAXX(FILTER('Sheet4 (2)','Sheet4 (2)'[ID]=ID2),'Sheet4 (2)'[SUBID]),'Sheet4 (2)'[SUBID]-1))
VAR newamount= MAXX(FILTER('Sheet4 (2)','Sheet4 (2)'[ID]=ID2&&'Sheet4 (2)'[SUBID]=SUBID2),'Sheet4 (2)'[Amount])
RETURN 'Sheet4 (2)'[Amount]/newamount

    hope this is helpful

    • MrDarian's avatar
      MrDarian
      Helper II
      6 years ago

      YOUR RESPONES WORKED. BUT I NEED MEASURE INSTEAD OF CALCULATED COLUMN

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi MrDarian ,

         

        Hope you were able to see my last response.

         

        It is a measure.

         

        Regards,

        Harsh Nathani