Forum Discussion

jochungJE's avatar
jochungJE
Frequent Visitor
7 years ago
Solved

Ranking Column by Supplier Grouping

Hi,

I am new to Power BI and I am hoping I can get some help with ranking issue. Thanks in advance for all your help with this question.

 

I have a table that contain supplier score by category and measurement. I need to rank the supplier by the total score for the period. The score is break down by Category and Measurement.

 

I tried the with RANKX as follow:

Ranking =
RANKX(
    ALLSELECTED('Table'[Supplier ]),
    CALCULATE(SUM('Table'[GroupScore])),
    ,
    DESC,Dense
    )
 
When I look at the table visual with just the supplier and the total score, it gives me the correct rankin by the supplier as shown.   

 

 

 

 

 

 

 

 

 

 

If I bring in the Category and the Measures into the table, my ranking is now no longer by supplier, but by Measurement as shown below. How do I apply the filter so no mater how I slide the data table, it will give me the correct ranking by supplier as shown above.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi jochungJE ,

    For your requirement, please refer to the formulas below.

    Measure =
COUNTROWS (
    FILTER (
        ALL ( new ),
        ISONORAFTER (
                new[sum of score], SELECTEDVALUE ( new[sum of score] ), DESC,
                new[Supplier], SELECTEDVALUE ( new[Supplier] ), DESC
        )
    )
)

Measure 2 = RANKX(ALL(new),[Measure],,DESC,Dense)

    Here is the output.

    More details, please refer to my attachment.

    Best Regards,

    Cherry

     

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi jochungJE ,

    Please try this measure below.

    Ranking =
RANKX (
    ALLSELECTED ( 'Table'[Supplier], 'Table'[Category], 'Table'[Measurement] ),
    CALCULATE ( SUM ( 'Table'[Score] ) ),
    ,
    DESC,
    DENSE
)

    Here is my test output.

    If you still need help, please share your desired output.

    Best  Regards,

    Cherry

     

    • jochungJE's avatar
      jochungJE
      Frequent Visitor
      7 years ago

       

      Hi Cherry,

       

      See below for the desired output, I am not able to replicate this in Power BI.

      The raking is for the total score of the Supplier for the Period and Group, disregard the category and measurement.

       

      Thanks in advance.

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Hi jochungJE ,

        For your requirement, please refer to the formulas below.

        Measure =
COUNTROWS (
    FILTER (
        ALL ( new ),
        ISONORAFTER (
                new[sum of score], SELECTEDVALUE ( new[sum of score] ), DESC,
                new[Supplier], SELECTEDVALUE ( new[Supplier] ), DESC
        )
    )
)

Measure 2 = RANKX(ALL(new),[Measure],,DESC,Dense)

        Here is the output.

        More details, please refer to my attachment.

        Best Regards,

        Cherry