RANKX with Hard Coded Filter vs. Slicers or Report Filters

[colindarling]

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)

[Sean]

@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
)

[Vvelarde]

@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.

[Sean]

@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
)

[colindarling]

@Sean - Your second example appears correct in the screenshots. However, I may have oversimplified my example. In my real data, I have other fields that play a role (like month for example. As a calculated column, I am not returning the same results, it appears to have an aggregation issue (there are ranking numbers higher than the number of managers).

Is there any way to write exactly what you have in the second set of screenshots, but as a measure instead of Column? I basically want my formula to understand that there is a 1-1 relationship between manager and VP. Therefore, is there a way to rank within VP based on the Manager name being part of the report?

[colindarling]

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!

[v-sihou-msft · Accepted solution]

@colindarling

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:

1. Go to the Query Editor, right click the ORG table – Duplicate – Remove columns of Location and RFD – Remove duplicates of the remaining AVP group – Add index column from 1. Then the newly duplicated “VPMark” table will like below. Close and apply the Query Editor.
   
2. Add a new column for “VPMark” table with formula: GroupIndex = "G" & VPMark[Index]. Then the “VPMark” table will like below.
   
3. Now there will be a Many to One relationship between ORG and VPMark table.
   
4. Create a new column in ORG table with formula: AVPGroup = RELATED(VPMark[GroupIndex])
   
5. Create two measures as 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] ) )
   )
   

6. Then VP names will be replaced with predefined VP groups in table chart.
   

Regards,

[Vvelarde]

@Sean please can you explain how works ALLEXCEPT?.

Thanks a lot