Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
shanestocks
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_numberreq_qtyp_codemeat_codestandard_pricemeat_price
31056389802P10000RM00234.984.605
31056695406P10000RM00234.984.605
31056987812P10000RM00234.984.605
31057316782P10000RM00234.984.605
31057638812P10000RM00234.984.605
31057735401P10000RM00234.984.605
31058261406P10000RM00234.984.605
31058871396P10000100826334.984.98
31059168596P10000RM00234.984.605
310593281163P10000100826334.984.98
31059962752P10000RM00234.984.605
31060099752P10000100826334.984.98
310606901203P10000RM00234.984.605
31061129812P10000RM00234.984.605
310614031168P10000100826334.984.98
310618821131P10000100826334.984.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:

 

shanestocks_1-1709195614626.png

 

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:

 

shanestocks_0-1709196425724.png

 

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:

 

shanestocks_0-1709196828044.png

 

 

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.

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
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:

vheqmsft_0-1709258989893.png

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

vheqmsft_2-1709259177617.png

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

 

View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
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:

vheqmsft_0-1709258989893.png

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

vheqmsft_2-1709259177617.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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