cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
colindarling
Regular Visitor

RANKX with Hard Coded Filter vs. Slicers or Report Filters

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:

DAX.PNG 

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.

 

ReportExample.PNG

 

 

However, our desired report view looks like this (No AVP grouping on the left hand side).

 

ReportExampleDesired.PNG

When AVP is removed from the report (or is not filtered) the result from the above formula looks like this:

 

ReportExample2.PNG

 

  

This is what the two tables look like: (Example)

 

Tables.PNG

 

1 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.
    1.png
  2. Add a new column for “VPMark” table with formula: GroupIndex = "G" & VPMark[Index]. Then the “VPMark” table will like below.
    2.png
  3. Now there will be a Many to One relationship between ORG and VPMark table.
    3.jpg
  4. Create a new column in ORG table with formula: AVPGroup = RELATED(VPMark[GroupIndex])
    4.png
  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.
    5.png

 

Regards,

 

View solution in original post

7 REPLIES 7
Sean
Community Champion
Community Champion

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

 

RANKX - Managers and VPs.png

Vvelarde
Community Champion
Community Champion

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

 

 

 

 




Lima - Peru
Sean
Community Champion
Community Champion

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

 

RANKX - Managers and VPs2.png

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

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!

 

 



@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.
    1.png
  2. Add a new column for “VPMark” table with formula: GroupIndex = "G" & VPMark[Index]. Then the “VPMark” table will like below.
    2.png
  3. Now there will be a Many to One relationship between ORG and VPMark table.
    3.jpg
  4. Create a new column in ORG table with formula: AVPGroup = RELATED(VPMark[GroupIndex])
    4.png
  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.
    5.png

 

Regards,

 

View solution in original post

Vvelarde
Community Champion
Community Champion

@Sean please can you explain how works ALLEXCEPT?.

 

Thanks a lot




Lima - Peru

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors