Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there!
I have used the below dax expression for Top 5 Managers by Commision, which worked,
Above is the result, which is fine, but there are two problems:
Solved! Go to Solution.
Hi @Ahsan_ ,
1. The reason you are getting the full total in the total row is because there is no concept of a manager filtering this value so there is nothing to rank. To remedy this you can create a new measure that iterates over the all the values of manager and adds them up.
Top 5 Managers Enhanced =
SUMX(
VALUES( Store[Manager Name] ),
[Top 5 managers]
)
2. When you add Store onto the axis that adds store to the filter context of the rank. All managers will be the top ranked for their store (assuming one manager per store) so they all show. I don't know the intricacies of the model but you could extend the above to state
Top 5 Managers Enhanced =
CALCULATE(
SUMX(
VALUES( Store[Manager Name] ),
[Top 5 managers]
),
ALL( Store[Store Name] )
)
I've assumed there that Store Name is in the same Store table as manager.
If you want it all in the same measure you could copy paste your original code in but make sure to wrap it in a CALCULATE so that context transition takes place
Top 5 Managers Enhanced =
CALCULATE(
SUMX(
VALUES( Store[Manager Name] ),
CALCULATE(
VAR ranking = RANKX(ALL(Store[Manager Name]),[Sales Commission],,DESC)
RETURN
IF(ranking <=5,[Sales Commission])
)
),
ALL( Store[Store Name] )
)
Hope it helps,
Kris
It worked like magic! Thanks
Hi @Ahsan_ ,
1. The reason you are getting the full total in the total row is because there is no concept of a manager filtering this value so there is nothing to rank. To remedy this you can create a new measure that iterates over the all the values of manager and adds them up.
Top 5 Managers Enhanced =
SUMX(
VALUES( Store[Manager Name] ),
[Top 5 managers]
)
2. When you add Store onto the axis that adds store to the filter context of the rank. All managers will be the top ranked for their store (assuming one manager per store) so they all show. I don't know the intricacies of the model but you could extend the above to state
Top 5 Managers Enhanced =
CALCULATE(
SUMX(
VALUES( Store[Manager Name] ),
[Top 5 managers]
),
ALL( Store[Store Name] )
)
I've assumed there that Store Name is in the same Store table as manager.
If you want it all in the same measure you could copy paste your original code in but make sure to wrap it in a CALCULATE so that context transition takes place
Top 5 Managers Enhanced =
CALCULATE(
SUMX(
VALUES( Store[Manager Name] ),
CALCULATE(
VAR ranking = RANKX(ALL(Store[Manager Name]),[Sales Commission],,DESC)
RETURN
IF(ranking <=5,[Sales Commission])
)
),
ALL( Store[Store Name] )
)
Hope it helps,
Kris
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |