Dax IF with RankX (Part 2)

[tomshaw83]

Hi all,

Thanks to the community for the help with my first ever query Dax IF with Rankx  

A recap on what was achieved in the above

1) A table such as the below can be dynamically ranked based on variables (gender, store, department) 

2) Based on that rank the 'bonus' appears to the top 4 sales staff (sorted below on store)

Salestable

Name	Store	Department	Gender	Sales	Staffdynamicrank	Bonus	Newbonus
Sarah Salmon	Highways	Electrical	Female	$1500	1	$200
Steve Breen	Highways	Fishing	Male	$1400	2	$150
Colin Codfish	Highways	Electrical	Male	$1000	3	$100
Claire Perch	Highways	Garden	Female	$900	4	$50
Sally Snapper	Highways	Homeware	Female	$450	5	$0

What I'm looking to achieve now is that if the top sales member for any selection, has sales greater than $200 more than their peers, then they take their bonus - Newbonus. So in the example below Sarah steals Colin's and Claire's bonus.

Salestable

Name	Store	Department	Gender	Sales	Staffdynamicrank	Bonus	Newbonus
Sarah Salmon	Highways	Electrical	Female	$1500	1	$200	$350
Steve Breen	Highways	Fishing	Male	$1400	2	$150	$150
Colin Codfish	Highways	Electrical	Male	$1000	3	$100	$0
Claire Perch	Highways	Garden	Female	$900	4	$50	$0
Sally Snapper	Highways	Homeware	Female	$450	5	$0	$0

I only have a very vague understanding - the below is where I have got to with a new measure...

Newbonus =

sumx(

Salestable,

if([Staffdynamicrank]=1

&& Salestable[Sales] ..........

From here I'm confused as I want to somehow to say that the difference >$200, then add that amount to the original bonus. TBH I don't know if I should be using IF or SWITCH (or another function altogether). 

Any help, or pointers where I can do more homework, would be greatly appreciated.

Thanks 

Tom

[tomshaw83]

@CNENFRNL Thank you again for your time

[CNENFRNL · Accepted solution]

Hi, @tomshaw83 , since you start the episode 2 (lucky that my reply to episode 1 got accepted as solution 😃), I'd put my solution in play. This time, it's a calculated column,

NewBonus = 
VAR __TopSales = MAXX ( Sales, Sales[Sales] )
VAR __inferior = FILTER ( Sales, __TopSales - Sales[Sales] >= 200 )
RETURN
    SWITCH (
        TRUE (),
        Sales[Sales] = __TopSales, Sales[Bonus] + SUMX ( __inferior, Sales[Bonus] ),
        Sales[Sales] > MAXX( __inferior, Sales[Sales] ), Sales[Bonus],
        0
    )

Measure solution is also available; you might want to refer to the attched file for details.