Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |