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.
