Dynamic Rankx Measure

[spartanboy]

Hello Experts!

In the below pbi snippet, please help in creating a dynamic ranking, expectation here is the rank for each months should change with drill up or drill down.

The ranking is supposed to be per SKU_ID & COST_TYPE, ranked by suppliers. At the end, the matrix should answer how the Suppliers are ranking against other suppliers for a given SKU_ID/COST_TYPE/TIME_FRAME

Access linke: https://www.dropbox.com/s/8q0d95e5edp5wze/Sample_PBI.pbix?dl=0

Thanks,

@amitchandak 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measure and the attached pbix file.

Ranking measure: =
SWITCH (
    TRUE (),
    ISINSCOPE ( Sample_Data[MFG_ID] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    ALL ( Sample_Data ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[REGION] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[COST_TYPE],
                        Sample_Data[REGION],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[COST_TYPE] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[COST_TYPE],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[COST_GROUP] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[SKU_ID] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE ( ALL ( Sample_Data ), Sample_Data[SKU_ID], Sample_Data[TIME_FRAME] ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        )
)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measure and the attached pbix file.

Ranking measure: =
SWITCH (
    TRUE (),
    ISINSCOPE ( Sample_Data[MFG_ID] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    ALL ( Sample_Data ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[REGION] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[COST_TYPE],
                        Sample_Data[REGION],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[COST_TYPE] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[COST_TYPE],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[COST_GROUP] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE (
                        ALL ( Sample_Data ),
                        Sample_Data[SKU_ID],
                        Sample_Data[COST_GROUP],
                        Sample_Data[TIME_FRAME]
                    ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        ),
    ISINSCOPE ( Sample_Data[SKU_ID] ),
        IF (
            [Calculate_WT_AVG_COST] <> BLANK (),
            RANKX (
                FILTER (
                    SUMMARIZE ( ALL ( Sample_Data ), Sample_Data[SKU_ID], Sample_Data[TIME_FRAME] ),
                    [Calculate_WT_AVG_COST] <> BLANK ()
                        && Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
                ),
                [Calculate_WT_AVG_COST],
                ,
                DESC
            )
        )
)

[spartanboy]

@Jihwan_Kim : It all works, greatly appreciated! One last: What-if the "REGION" is coming from another table? on just changing all the ranking are displayed as 1. Please help..

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Please check the attached file, if I correctly created a different table for a Region table.

Thanks.

[spartanboy]

Thanks for providing the solution. One final request - How can i NOT rank a supplier (eg: Supplier 1).

Attached file.

https://www.dropbox.com/s/8q0d95e5edp5wze/Sample_PBI.pbix?dl=0

[Jihwan_Kim]

Hi,

I am not sure if I understood correctly, but when I saw the file in the link, I think ranking except supplier3 was tried.

I tried to create a measure like below, and please check the attached file if it suits the requirement.

Ranking except Supplier_3: =
VAR _newtable =
    FILTER (
        SUMMARIZE (
            ALL ( Sample_Data ),
            Sample_Data[PRODUCT_GROUP],
            Sample_Data[SKU_ID],
            Sample_Data[COST_GROUP],
            Sample_Data[COST_TYPE],
            Sample_Data[MFG_ID],
            Sample_Data[TIME_FRAME]
        ),
        Sample_Data[TIME_FRAME] = MAX ( Sample_Data[TIME_FRAME] )
            && Sample_Data[COST_TYPE] = MAX ( Sample_Data[COST_TYPE] )
            && Sample_Data[COST_GROUP] = MAX ( Sample_Data[COST_GROUP] )
            && Sample_Data[SKU_ID] = MAX ( Sample_Data[SKU_ID] )
            && Sample_Data[PRODUCT_GROUP] = MAX ( Sample_Data[PRODUCT_GROUP] )
            && Sample_Data[MFG_ID] <> "SUPPLIER_3"
    )
RETURN
    IF (
        SELECTEDVALUE ( Sample_Data[MFG_ID] ) <> "SUPPLIER_3",
        RANKX ( _newtable, [Calculate_WT_AVG_COST],, ASC )
    )