Forum Discussion
Top 5 By Two different Conditions
Hi Everyone, I have a set of data given below. Iwant top 5 assoicate name by spend as well by division. For example the division (Top5 would be in Column) and associate name would be in Rows in M...
Mariusz
Community Champion
Community ChampionHi Anonymous
Try this Measure
SpendTop5=
VAR __topN = 5
VAR __associate =
RANKX (
ALL ( 'Table'[Associate] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Associate] ) ),
,
DESC,
DENSE
) <= __topN
VAR __division =
RANKX (
ALL ( 'Table'[Division] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Division] ) ),
,
DESC,
DENSE
) <= __topN
RETURN
IF( __division && __associate, [spend] )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi Mariusz,
Are these two seperate measures? I tried to create only one measure and not getting the desired Output i.e
SpendTop5= VAR __topN = 5 VAR __associate = RANKX ( ALL ( 'Table'[Associate] ), CALCULATE( [spend], ALLEXCEPT( 'Table', 'Table'[Associate] ) ), , DESC, DENSE ) <= __topN VAR __division = RANKX ( ALL ( 'Table'[Division] ), CALCULATE( [spend], ALLEXCEPT( 'Table', 'Table'[Division] ) ), , DESC, DENSE ) <= __topN RETURN IF( __division && __associate, [spend] )See I am getting below output from your given DAX
Apologies, As I am noob in DAX queries.
Kindly Help.