cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Sum of matrix rows

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:

 

SKUWeekPlanActualsDeviationAbs. Deviation
A110090-1010
A21001202020
B1505555
B25035-1515
Totals SKU A-2002101010
Totals SKU B-10090-1010
Totals-30030000

 

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")
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sum of matrix rows

Hi ErnstBuiteman,

If you want to interactive with slicer, you could try to follow bellow steps:

I dont know your detailed sample data, so I assume that your data might be similar to below

8.png

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

9.png

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

10.png

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Sum of matrix rows

Hi ErnstBuiteman,

If you want to interactive with slicer, you could try to follow bellow steps:

I dont know your detailed sample data, so I assume that your data might be similar to below

8.png

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

9.png

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

10.png

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.

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 330 members 3,383 guests
Please welcome our newest community members: