Sum of matrix rows

[Anonymous]

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")

[dax · Accepted 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.