Ranking Column by Supplier Grouping

[jochungJE]

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.

[v-piga-msft]

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]

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 · Accepted solution]

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

[jochungJE]

Thank you @v-piga-msft ,

I need to modify both the Measure and Measure2 to include Period Key in the 'filter grouping'. Once I apply the new condition, the ranking is exactly what I needed.

Thank you so much for your support.  

Measure =

COUNTROWS (

FILTER (

        ALL ( new ),

ISONORAFTER (

                new[sum of score], SELECTEDVALUE ( new[sum of score] ), DESC,

                new[Supplier], SELECTEDVALUE ( new[Supplier] ), DESC,

                new[Period Key], SELECTEDVALUE(new[Period Key]),DESC

        )

    )

)

Measure 2 = RANKX(

    ALLEXCEPT(new,new[Period Key]),

    [Measure],

    ,

    DESC,

    Dense

    )