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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Greg, 

Thank you for looking at it. This issue has been been giving me a headache for some time now. 

 

- Zar

Greg_Deckler 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

You can create a calculated column and a measure to achieve it:

Rank = RANKX(ALLSELECTED('Employees'),CALCULATE([Sales Employee Type]),,ASC,Dense)
Rank by Sales Employee Type = RANKX(FILTER(ALLSELECTED('Employees'),'Employees'[Rank]=MAX('Employees'[Rank])),[Total Sales],,ASC,Dense)

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous , 

     

    Thank you for your reply. This is a good solution, however becuase calculated columns only update at refresh time, using one will not solve the issue in my case.

     

    I need the subcategory, 'Sale Employee Type' to be calculated based on the Start and End Date slicer selection, and then the employees to be rank.  Do you know of a work around that does not require the use of a calculated column? 

     

    Thank you, 

     

    Anonymous amitchandak Greg_Deckler 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

      I'm so sorry I didn't find other alternative solution to create a measure that sort the data base on the measure [Sales Employee Type] currently...

      mahoneypat , Ashish_Mathur  Could you please help check the following problem? You can download the sample pbix file from the this link. Thank you in advance.

      Best Regards

      Rena

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        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