Dax IF with Rankx

[tomshaw83]

Hello everyone,

Complete newbie to Powerbi here, I'm wrestling with some Dax code that I hoped someone could help me with....

Salestable

Name	Store	Department	Gender	Sales
Joe Bloggs	Highways	Electrical	Male	$550
Sam Smith	Chandler	Homeware	Male	$400
Susan Brown	Highways	Homeware	Female	$350
Jenny Shaw	Highways	Garden	Female	$900
Gordon Murphy	Chandler	Electrical	Male	$500

The first thing I wanted to do was to create a dynamic measure that changed depending on the variables (Store, Department, Gender)

I used the code

Staffdynamicrank = RANKX(ALLSELECTED(Salestable),CALCULATE(SUM(Salestable[Sales])),,DESC,Dense)

I'm not 100% sure what all the syntax means but it does seem to do what I want... The below is filtered just on store

Name	Store	Department	Gender	Sales	Staffdynamicrank
Jenny Shaw	Highways	Garden	Female	$900	1
Joe Bloggs	Highways	Electrical	Male	$550	2
Susan Brown	Highways	Homeware	Female	$350	3

However, from here it gets complicated, I want to create a bonus column for the top 4 based on the current filter chosen by the end user, so if ranked 1 bonus = $200, 2 - $150, 3 - $100, 4 - $50

I am trying to do something along the lines of

Bonus = sumx(

Salestable,

IF([Staffdynamicrank]=1,$200,0))

I can't get this to work just for one bonus...:(

I'd like to do some more complicated things, based on the amount that the staff member has earned - if they are so far ahead of the other people they get their bonus added on as well, but for now I'd just like to be able to use the IF function with the measure I have.

Thanks

Tom

[CNENFRNL · Accepted solution]

Hi, @tomshaw83 , you're only an inch away from your goal of bonus calculation. You might want to try the following measure,

Bonus = SWITCH ( [Staffdynamicrank], 1, 200, 2, 150, 3, 100, 4, 50 )

One point worth paying attention to is your measure

Staffdynamicrank = RANKX( ALLSELECTED(Sales[Name]), ... )

is relative ranking since you might want them to be dynamic. Thus, any other measures referencing the rank measure also work in a relative manner.

[tomshaw83]

Spot on!, Thanks @CNENFRNL !!