cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Weighted Average by Code

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!

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Regular Visitor

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.

Regular Visitor

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors