Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi guys, I am having an issue with correcting the weighted average in a calculation. I have a hierarchy, P code, and then meat code. Multiple meat codes can make up a P code, and each meat code has it's own price. Depending on what meat code is used affects the price of the P code.
The raw data is as follows for 1 code:
order_number | req_qty | p_code | meat_code | standard_price | meat_price |
31056389 | 802 | P10000 | RM0023 | 4.98 | 4.605 |
31056695 | 406 | P10000 | RM0023 | 4.98 | 4.605 |
31056987 | 812 | P10000 | RM0023 | 4.98 | 4.605 |
31057316 | 782 | P10000 | RM0023 | 4.98 | 4.605 |
31057638 | 812 | P10000 | RM0023 | 4.98 | 4.605 |
31057735 | 401 | P10000 | RM0023 | 4.98 | 4.605 |
31058261 | 406 | P10000 | RM0023 | 4.98 | 4.605 |
31058871 | 396 | P10000 | 10082633 | 4.98 | 4.98 |
31059168 | 596 | P10000 | RM0023 | 4.98 | 4.605 |
31059328 | 1163 | P10000 | 10082633 | 4.98 | 4.98 |
31059962 | 752 | P10000 | RM0023 | 4.98 | 4.605 |
31060099 | 752 | P10000 | 10082633 | 4.98 | 4.98 |
31060690 | 1203 | P10000 | RM0023 | 4.98 | 4.605 |
31061129 | 812 | P10000 | RM0023 | 4.98 | 4.605 |
31061403 | 1168 | P10000 | 10082633 | 4.98 | 4.98 |
31061882 | 1131 | P10000 | 10082633 | 4.98 | 4.98 |
The "standard price" is the cost of 1 P Code. The "meat_price" is the price of 1 meat, and in the above example a site has a choice of two meats, one costs £4.605, and the other £4.98. The req_qty is how many meat was required for that order. The weighted average, I believe, is as follows:
SUM (req_qty * meat_price) / SUM (req_qty) = 4.744. Therefore, the standard of this P code is 4.98, but the actual is 4.744.
The DAX calculation I have created absolutely works fine when we are looking only at a specific code at a time as follows:
However, what then happens is that the "variance" between the two, appears to be slightly off (and way off in the grand total). The calculation for the variance is as follows:
Actual Price - Standard Price * Requirement. In the same P code example below, the following is displayed:
So at the meat level, the calculation (4.605 - 4.980) * 7,783 = 2,919 works perfectly,
BUT AT THE P CODE LEVEL - (4.744 - 4.980) * 12,392 = 2,924 which is incorrect. It is THIS that I need to get correct, because the end result I would like only the P codes, not the meat codes:
The DAX I am using for the Act. (EUR) calculation is:
VAR weightedPrice = SUMX(fUsage,fUsage[req_qty] * fUsage[meat_price])
VAR reqTotal = sum(fUsage[req_qty])
RETURN
DIVIDE(weightedPrice,reqTotal)
The DAX I am using for the Sub (VAR) calculation is:
([Act. (EUR)] - [Std. (EUR)]) * [Req (KG)]
Any help would be greatly appreciated, appears I need to add something extra here but I cannot figure out what!
Thank you so much in advance, please let me know if any additional context is required.
Solved! Go to Solution.
Hi @shanestocks ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Based on the measure you created I created a variance measure
variance = ABS(([Act.]-SELECTEDVALUE(fUsage[standard_price]))*SUM(fUsage[req_qty]))
The act measure you wrote before is correct.
Final output
If the variance that ultimately shows 2919 at the p_code level is not correct, could you please provide more details on how Sub,Req was created, preferably in a pbix file, please hide private information in advance, and the same please provide your expected results in excel
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @shanestocks ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Based on the measure you created I created a variance measure
variance = ABS(([Act.]-SELECTEDVALUE(fUsage[standard_price]))*SUM(fUsage[req_qty]))
The act measure you wrote before is correct.
Final output
If the variance that ultimately shows 2919 at the p_code level is not correct, could you please provide more details on how Sub,Req was created, preferably in a pbix file, please hide private information in advance, and the same please provide your expected results in excel
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Wow this works, thank you. I would love to know how the SELECTEDVALUE clause has corrected this. I am using Power Pivot in Excel, I don't think this is there would there be any alternative, or perhaps I'll need to recreate in BI.
Thank you I will attempt this and let you know.
Ultimately it appears at the P level the calculations are not correct, but at the individual meat levels they are.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
141 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
212 | |
89 | |
76 | |
66 | |
60 |