Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rank Based on a Switch Sub Category Measure

Hello,
Is it possible to use a measure's output as a filter? In my model, depending on how many sales an employee has of a certain product in a specified time period determines the type of sales rep that the employee is, so this is why I have to use a measure to calculate what type of sales rep the employee is.

I have attached a link to download the PBIX file that describes the end goal in more detail with dummy data. 

https://github.com/Zar117/Power-BI-Rank-Based-On-Switch-Measure.git 
I am open to any solution. 

 

The switch statement that I wish to rank on: 

 

 

Below is a screen shot of the desired end result. 

 

I really appreciate any help, thanks in advance. 
- Zar 

@EnterpriseDNA

 

Need Help
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is an approach that creates a virtual table and then filters it to employees of the same type (in the selected context), and then the rank is determined using the Value expression in the RANKX formula.  Please try this measure expression.  I'm not sure why it doesn't match your rank values, but it does calculate rank within each type group.  You can adapt it to get your desired result.  Did you rank based on Employee ID?

     

    Rank by Type =
VAR currenttype = [Sales Employee Type]
VAR overallrank = [Overall Rank by Employee ID]
VAR summary =
    ADDCOLUMNS (
        ALLSELECTED ( Employees[Employee ID] ),
        "@type", [Sales Employee Type],
        "@rank", [Overall Rank by Employee ID]
    )
RETURN
    RANKX ( FILTER ( summary, [@type] = currenttype ), [@rank], overallrank, ASC )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

8 Replies