topic Introduce a dynamic rank function based on user selection of calendar periods in Developer

Introduce a dynamic rank function based on user selection of calendar periods

Anonymous — Mon, 18 Jul 2022 15:35:34 GMT

To Introduce a dynamic rank function based on user selection of calendar periods (one each for Current Period and Prior Period) and the ranking of a dynamic changing weighted average costs measure.

1)Current Period: For the user selected months (multiple selection enabled slicer) in the slicer the maximum of the selected month is the current period.

2)Prior period: For the user selected months (multiple selection enabled slicer) in the slicer the minimum of the selected month is the current period.

3)Weighted Average Cost:

For calculating Weighted average cost we have two measures in practice

Calculate_TOTAL_COST = sum(F_WRIN_SUPPLIER_CSA_COST[COST_PER_UOM])*if(SUM(F_WRIN_SUPPLIER_CSA_COST[UOM_QTY_SHIPPED])==0,1,SUM(F_WRIN_SUPPLIER_CSA_COST[UOM_QTY_SHIPPED]))

Calculate_WT_AVG_COST = (

VAR qty = SUM(_SUPPLIER_CSA_COST[UOM_QTY_SHIPPED])

VAR SummarizedTable =

ADDCOLUMNS(

SUMMARIZE(

_SUPPLIER_CSA_COST,

D_SUPPLIERS[SUPPLIER_NAME],D_WRIN[WRIN_DESCRIPTION],F_WRIN_SUPPLIER_CSA_COST[TIME_FRAME]

"totalcost", [Calculate_TOTAL_COST],

"qty", qty

)

return

sumx(

SummarizedTable,

[totalcost]/[qty]

)

SUPPLIER_CSA_COST Table have cost component details and the rest info we are getting from multiple dim tables.

Re: Introduce a dynamic rank function based on user selection of calendar periods

Anonymous — Thu, 21 Jul 2022 09:30:45 GMT

Hi Anonymous ,

I think your calculation is based on your data model. Please share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Introduce a dynamic rank function based on user selection of calendar periods

Anonymous — Mon, 25 Jul 2022 12:50:59 GMT

Hi Rico,

Thanks for your response.

Please find the below sample data

F_WRIN_SUPPLIER_CSA_COST table:

idx

CATEGORY

SUPPLIER_ID

WRIN_ID

COST_TYPE

COMPONENT

COMPONENT_TYPE

UOM

COST_PER_UOM

UOM_QTY_SHIPPED

TIME_PERIOD

SUPPLIER_RANK

TIME_FRAME

TIME_TYPE

47526

BUN

SUPPLIER_19182

WRIN_00001621

FOB

UTILITIES

CONVERSION

$0.0254

Actual

2021-07-01

Monthly

47514

BUN

SUPPLIER_19182

WRIN_00001621

FOB

UTILITIES

CONVERSION

$0.0254

Actual

2021-10-01

Monthly

47502

BUN

SUPPLIER_19182

WRIN_00001621

FOB

UTILITIES

CONVERSION

$0.0731

Actual

2022-01-01

Monthly

47550

BUN

SUPPLIER_19182

WRIN_00001621

FOB

UTILITIES

CONVERSION

$0.0254

2,11,59,120

Actual

2021-01-01

Monthly

47538

BUN

SUPPLIER_19182

WRIN_00001621

FOB

UTILITIES

CONVERSION

$0.0254

2,25,21,456

Actual

2021-04-01

Monthly

Wrin_prefix table dim

idx	WRIN_ID	PREFIX_ID
166	WRIN_00001621	PREFIX_00001

Prefix table dim:

idx	PREFIX_ID	PREFIX_ALIAS	PREFIX
0	PREFIX_00001	1	00001-REGULAR BUNS

Supplier table Dim:

idx	SUPPLIER_ID	SUPPLIER_ALIAS	SUPPLIER_NAME	SUPPLIER_PARENT_NAME	SUPPLIER_ZIPCODE	SUPPLIER_CITY	SUPPLIER_STATE	SUPPLIER_COUNTRY	TIME_ZONE	REGION
24	SUPPLIER_19182	19182	19182-Foods INC.	FOODS, INC.	27520	CLAYTON	Austin	UNITED STATES	Eastern Standard Time (EST)	EAST