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.
I have a database where on a weekly basis productions plans and actuals are being submitted. Reporting of production adherence needs to happen every 2 weeks. The problem I have occurs when I have a SKU for both weeks in production. An example below:
SKU | Week | Plan | Actuals | Deviation | Abs. Deviation |
A | 1 | 100 | 90 | -10 | 10 |
A | 2 | 100 | 120 | 20 | 20 |
B | 1 | 50 | 55 | 5 | 5 |
B | 2 | 50 | 35 | -15 | 15 |
Totals SKU A | - | 200 | 210 | 10 | 10 |
Totals SKU B | - | 100 | 90 | -10 | 10 |
Totals | - | 300 | 300 | 0 | 0 |
The reporting happens on a SKU level (so the combination of week 1 and 2 together). For this I have created a measure to calculate deviation and absolute deviation. The individual lines in the matrix are presented correctly. However In the totals the measure is calculated as 300 against 300 resulting in 0. I do want to have a measure which sums up all the summarized lines for each SKU. My expected result would be in this case (10+10=20) for absolute deviation. Doesn't need to be inside the matrix itself, can also be a card visual.
The selection of weeks happens with a slicer. I was able to create the expected output with the SUMMARIZE function, but not able to link it to interaction of the slicer input.
PlanTable = var SelectedPeriod = SELECTEDVALUE(Periods[PeriodID]) return CALCULATETABLE( SUMMARIZE ( a010_PlanFulfilment; a010_PlanFulfilment[ItemID]; a010_PlanFulfilment[PlantID]; "PlanQTY"; CALCULATE ( SUM ( a010_PlanFulfilment[Quantity] ); FILTER ( a010_PlanFulfilment; a010_PlanFulfilment[DataTypeID] = "PLA" ) ); "ActQTY"; CALCULATE ( SUM ( a010_PlanFulfilment[Quantity] ); FILTER ( a010_PlanFulfilment; a010_PlanFulfilment[DataTypeID] = "ACT" ) )); a010_PlanFulfilment[PeriodID] = "201914" || a010_PlanFulfilment[PeriodID] = "201915")
Solved! Go to Solution.
Hi ErnstBuiteman,
If you want to interactive with slicer, you could try to follow bellow steps:
I don’t know your detailed sample data, so I assume that your data might be similar to below
planfulfilment = CALCULATETABLE( SUMMARIZE ( SKU2, SKU2[SKU],SKU2[Week],SKU2[PeriodID], "PlanQTY", CALCULATE ( SUM ( SKU2[Quantity] ), FILTER (SKU2, SKU2[DataTypeID]= "PLA" ) ), "ActQTY", CALCULATE ( SUM ( SKU2[Quantity] ), FILTER (SKU2, SKU2[DataTypeID]= "ACT" ) )))
Then create below measures
sku2absdev = SUMX ( SUMMARIZE ( 'planfulfilment', planfulfilment[SKU], "ABSDEV", ABS ( SUM ( 'planfulfilment'[ActQTY] ) - SUM ( 'planfulfilment'[PlanQTY] ) ) ), [ABSDEV] )
SKU2DEV = SUMX ( SUMMARIZE ( 'planfulfilment', 'planfulfilment'[SKU], planfulfilment[Week], "DEV", SUM ( 'planfulfilment'[ActQTY] ) - SUM ( 'planfulfilment'[PlanQTY] ) ), [DEV] )
Then use planfulfilment[periodid] in slicer
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ErnstBuiteman,
If you want to interactive with slicer, you could try to follow bellow steps:
I don’t know your detailed sample data, so I assume that your data might be similar to below
planfulfilment = CALCULATETABLE( SUMMARIZE ( SKU2, SKU2[SKU],SKU2[Week],SKU2[PeriodID], "PlanQTY", CALCULATE ( SUM ( SKU2[Quantity] ), FILTER (SKU2, SKU2[DataTypeID]= "PLA" ) ), "ActQTY", CALCULATE ( SUM ( SKU2[Quantity] ), FILTER (SKU2, SKU2[DataTypeID]= "ACT" ) )))
Then create below measures
sku2absdev = SUMX ( SUMMARIZE ( 'planfulfilment', planfulfilment[SKU], "ABSDEV", ABS ( SUM ( 'planfulfilment'[ActQTY] ) - SUM ( 'planfulfilment'[PlanQTY] ) ) ), [ABSDEV] )
SKU2DEV = SUMX ( SUMMARIZE ( 'planfulfilment', 'planfulfilment'[SKU], planfulfilment[Week], "DEV", SUM ( 'planfulfilment'[ActQTY] ) - SUM ( 'planfulfilment'[PlanQTY] ) ), [DEV] )
Then use planfulfilment[periodid] in slicer
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |