Calculate Ratio

[MrDarian]

this is my table

i want to calculate ratio based on ID and Sub ID in a measure. in power bi

Like this:

[ryan_mayu]

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

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

[harshnathani]

Hi @MrDarian ,

Hope you were able to see my last response.

It is a measure.

Regards,

Harsh Nathani

[harshnathani]

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]

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

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

[harshnathani · Accepted solution]

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]

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

[amitchandak]

@MrDarian , if this not excel, what is logic to divide, odd even sub id?

[MrDarian]

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.

[Anonymous]

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]

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.