Forum Discussion
Helper IMeasure to calculate the absolute aggregate difference between planned vs sold per product each day
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:
- It iterates over each distinct RecipeID using the VALUES function.
- 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.
- Similarly, it calculates the SoldQty by summing the quantities from the 'SaleLine' table where the RecipeID matches the current RecipeID in context.
- It calculates the absolute difference between PlannedQty and SoldQty for each RecipeID.
- 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.
Community ChampionIt 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:
- It iterates over each distinct RecipeID using the VALUES function.
- 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.
- Similarly, it calculates the SoldQty by summing the quantities from the 'SaleLine' table where the RecipeID matches the current RecipeID in context.
- It calculates the absolute difference between PlannedQty and SoldQty for each RecipeID.
- 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.
Helper IThanks for your help