Measure to calculate the absolute aggregate difference between planned vs sold per product each day

[moesteez]

Hi,

Im trying to create a dax measure that will calculate the sum of the absolute differences of products planned and sold for each product on each day. This is quite easy on a spreadsheet but for some reason am struggling in Power BI.

This is my schema:

My table looks like this:

Im able to get the correct answer for eash line item but when it comes to the total Ive tried a few approaches and all seem to not work.

Ive looked at:

Difference =

 VAR planned = (sum('Planned Menus'[Qty]) )

 VAR sold =  sum('SaleLine'[Quantity])

 RETURN

 SUMX('Calendar', ABS(planned - sold ))

But I dont think is iterating through each recipeID maybe?

Any ideas how I can make this work?

Thanks

[123abc · Accepted solution]

It seems like you're trying to calculate the absolute aggregate difference between planned and sold quantities for each product on each day in Power BI using DAX. Your DAX measure looks mostly correct, but it seems you might need to adjust the context in which it operates. You should consider iterating over each product to calculate the absolute difference. Here's how you can modify your DAX measure:

AbsoluteDifference =
SUMX (
VALUES ( 'Sales'[RecipeID] ), -- Iterate over each distinct RecipeID
VAR PlannedQty =
CALCULATE (
SUM ( 'Planned Menus'[Qty] ),
FILTER ( 'Planned Menus', 'Planned Menus'[RecipeID] = 'Sales'[RecipeID] )
)
VAR SoldQty =
CALCULATE (
SUM ( 'SaleLine'[Quantity] ),
FILTER ( 'SaleLine', 'SaleLine'[RecipeID] = 'Sales'[RecipeID] )
)
RETURN
ABS ( PlannedQty - SoldQty )
)

Here's a breakdown of what this measure does:

1. It iterates over each distinct RecipeID using the VALUES function.
2. For each RecipeID, it calculates the PlannedQty by summing the quantities from the 'Planned Menus' table where the RecipeID matches the current RecipeID in context.
3. Similarly, it calculates the SoldQty by summing the quantities from the 'SaleLine' table where the RecipeID matches the current RecipeID in context.
4. It calculates the absolute difference between PlannedQty and SoldQty for each RecipeID.
5. Finally, it sums up these absolute differences to get the total absolute difference across all RecipeIDs for each day in the context of your report.

This measure should provide you with the absolute aggregate difference between planned and sold quantities for each product on each day in Power BI. Make sure to adjust the table and column names as per your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[moesteez]

Thanks for your help