Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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 | DZ | $0.0254 | 0 | Actual | 1 | 2021-07-01 | Monthly | |
47514 | BUN | SUPPLIER_19182 | WRIN_00001621 | FOB | UTILITIES | CONVERSION | DZ | $0.0254 | 0 | Actual | 1 | 2021-10-01 | Monthly | |
47502 | BUN | SUPPLIER_19182 | WRIN_00001621 | FOB | UTILITIES | CONVERSION | DZ | $0.0731 | 0 | Actual | 1 | 2022-01-01 | Monthly | |
47550 | BUN | SUPPLIER_19182 | WRIN_00001621 | FOB | UTILITIES | CONVERSION | DZ | $0.0254 | 2,11,59,120 | Actual | 1 | 2021-01-01 | Monthly | |
47538 | BUN | SUPPLIER_19182 | WRIN_00001621 | FOB | UTILITIES | CONVERSION | DZ | $0.0254 | 2,25,21,456 | Actual | 1 | 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 |
User | Count |
---|---|
16 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
22 | |
3 | |
3 | |
2 | |
2 |