Forum Discussion

spartanboy's avatar
spartanboy
Helper II
4 years ago
Solved

Dynamic Rankx Measure

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...
Need Help
Show and Tell
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message.

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

    Thanks.

spartanboy's avatar
spartanboy
Helper II
4 years ago

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's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Thank you for your message.

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

Thanks.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

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