Forum Discussion

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

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