Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have racked my brain on this subject and I'm finally asking for help. I reviewed a great article found on the forum:
http://www.powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/
I was able to replicate the formulas and get them to work. However, my team's request slightly deviates from this and I cannot figure out what needs to be done.
I have two tables. A fact table and an org dimension table (FCT-Many to ORG-One). We are trying to create two ranking measures. One that ranks the store managers (RFD) overall sales, and then one that ranks them within their Vice President Group (AVP). However, we want to hard code the "within" group and not show the Vice President Names on the report in Power BI itself. We also do not want to use any reporting filters unless we absolutely have to.
Any help would be appreciated. I'm certainly for the group, this is an easy one!
Thank you!
We are using this measure:
Please Note: FCT[Amount] is a Measure. sumx(FCT,FCT[Amount])
We get something back like this (RANK WITHIN AVP) which is working great if we want to show the AVP on our report.
However, our desired report view looks like this (No AVP grouping on the left hand side).
When AVP is removed from the report (or is not filtered) the result from the above formula looks like this:
This is what the two tables look like: (Example)
Solved! Go to Solution.
In this scenario, even the Manager and VP is 1:1 relationship, but when we lookupvalue() corresponding VP based on Manager column, it will return a column of values. However, it's not supported to aggregate a text column via DAX. Since you want to use measure, we must have both Manager and VP column to slice this measure. So I suggest you add a "dummy" column to replace VP column and use this column to slice the measure to get correct rank. Please refer to steps below:
Rank Overall = RANKX ( ALL ( ORG[RFD] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[RFD] ) ) ) Rank Within AVP = RANKX ( ALL ( ORG[RFD] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[RFD], ORG[AVPGroup] ) ) )
Regards,
@colindarling Create a Calculated Column in you ORG table and use it... (then you won't have to display/use the VP names)
Rank AVP Column = RANKX ( ALL ( ORG[AVP] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[AVP] ) ), , DESC, SKIP )
@Hi @Sean , am very interest in learn how to solve this.
Exist a issue; Your Column calculated (Rank AVP Column) gives the same Rank number for VP1 for both Managers.
@colindarling Maybe I didn't understand the original question.
But in any event this should solve the other scenario... which I think @Vvelarde is talking about.
This will Rank the Managers under each VP
Rank Manager within AVP Column = RANKX ( ALL ( ORG[RFD] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[AVP], ORG[RFD] ) ), , DESC, SKIP )
This is the beginner piece of information I think I needed.
"Since you want to use measure, we must have both Manager and VP column to slice this measure"
In order to use a measure, I need to have both Manager and VP involved to slice the measure (or a hard coded filter using the drag and drop, or hard coded value in my formula ex. Vice President 1). I cannot create a ranking measure and within that measure write filters behind the scenes that associate Manager with a specific VP Since they are 1 to 1?
I simply wanted to keep the information on my report as clear as possible:
Manager 1, you are ranked 5th overall, and 3rd under your VP. Three columns: Manager Name, Overall Rank, Rank within VP
Thanks so far for everyone who has chimed in to try and help!
In this scenario, even the Manager and VP is 1:1 relationship, but when we lookupvalue() corresponding VP based on Manager column, it will return a column of values. However, it's not supported to aggregate a text column via DAX. Since you want to use measure, we must have both Manager and VP column to slice this measure. So I suggest you add a "dummy" column to replace VP column and use this column to slice the measure to get correct rank. Please refer to steps below:
Rank Overall = RANKX ( ALL ( ORG[RFD] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[RFD] ) ) ) Rank Within AVP = RANKX ( ALL ( ORG[RFD] ), CALCULATE ( SUM ( FCT[Amount] ), ALLEXCEPT ( ORG, ORG[RFD], ORG[AVPGroup] ) ) )
Regards,
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |