Forum Discussion
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...
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 ) ) )Hi,
Thank you for your message.
Please check the attached file, if I correctly created a different table for a Region table.
Thanks.
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 : 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..
Hi,
Thank you for your message.
Please check the attached file, if I correctly created a different table for a Region table.
Thanks.
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
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 ) )