Comparing to a 'Previous Category'

[GouldM]

Supposing I have data in this table

Category   Value

1                3

2                6

3                7

How could I create a measure that DIVIDEs the value of a category but the value of the Previous category

Result would in the above be

Category Value Comparator

1               3       Blank()

2               6       2   (6/3)

3               7       2.16667

[bhanu_gautam · Accepted solution]

@GouldM , Create a measure using

DAX
Comparator =
VAR CurrentCategory = 'Table'[Category]
VAR PreviousCategoryValue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
'Table',
'Table'[Category] = CurrentCategory - 1
)
)
RETURN
IF(
ISBLANK(PreviousCategoryValue),
BLANK(),
DIVIDE('Table'[Value], PreviousCategoryValue)
)

[Ahmedx]

I advise you to try Visual calculation

watch the video
https://1drv.ms/v/c/916e1b3b6bd11925/ETgKUwdi8jBHgm614cvePPABaF_QRQg6jXqHfEEKuaQ22A?e=3dYdSy

[v-aatheeque]

Hi @GouldM 

If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[GouldM]

Hi none of the answers addressed my problem.

[ryan_mayu · Accepted solution]

@GouldM 

you can try this to create a column

Column =

var _last=maxx(FILTER('Table','Table'[category]=EARLIER('Table'[category])-1),'Table'[value])

return if(ISBLANK(_last),blank(),DIVIDE('Table'[value],_last))

or try this to create a measure

Measure =

var _last=maxx(FILTER(all('Table'),'Table'[category]=max('Table'[category])-1),'Table'[value])

return if(ISBLANK(_last),blank(),DIVIDE(max('Table'[value]),_last))

pls see the attachment below

[johnt75]

You could create a measure like

Comparator =
VAR CurrentValue = SUM( 'Table'[Value] )
VAR PrevValue = CALCULATE(
	SUM( 'Table'[Value] ),
	OFFSET(
		-1,
		ALLSELECTED( 'Table'[Category] ),
		ORDERBY( 'Table'[Category], ASC )
	),
	ALLEXCEPT( 'Table', 'Table'[Category] )
)
VAR Result = DIVIDE( CurrentValue, PrevValue )
RETURN Result

[bhanu_gautam · Accepted solution]

@GouldM , Create a measure using

DAX
Comparator =
VAR CurrentCategory = 'Table'[Category]
VAR PreviousCategoryValue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
'Table',
'Table'[Category] = CurrentCategory - 1
)
)
RETURN
IF(
ISBLANK(PreviousCategoryValue),
BLANK(),
DIVIDE('Table'[Value], PreviousCategoryValue)
)