Forum Discussion

AldoMF's avatar
AldoMF
Frequent Visitor
5 years ago
Solved

How do I get the difference between concepts using one as baseline?

I have this set of data in a matrix:

 

                        2017Q4      2018Q1      2018Q2    2018Q3    2018Q4     2019Q1     2019Q2   2019Q3       

Sell-out1,212,9451,137,895924,6481,036,5621,234,7061,145,306898,775966,646
Sell-through   689,881823,323635,203540,744576,637
Shipment1,445,8521,191,056944,5531,169,1601,346,8231,069,495911,6821,098,128

 

I would like to know the difference for each Q for Sell-Out and Sell-through vs Shipment. In excel I would only have to choose "show values as difference from" and select Shipment concept as the baseline and get something like this:

 

                         2017Q4       2018Q1       2018Q2     2018Q3    2018Q4      2019Q1     2019Q2    2019Q3

Sell-out1,212,9451,137,895924,6481,036,5621,234,7061,145,306898,775966,646
Sell-through   689,881823,323635,203540,744576,637
Shipment1,445,8521,191,056944,5531,169,1601,346,8231,069,495911,6821,098,128
Sell-out-232,907-53,161-19,905-132,598-112,11775,811-12,907-131,482
Sell-through-1,445,852-1,191,056-944,553-479,279-523,500-434,292-370,938-521,491
Shipment        

 

Is there a formula I can use to replicate this same process?

General Comment
Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    5 years ago

    AldoMF,

     

    Try this solution.

     

    1. Structure the data like this (you can use Unpivot in Power Query if each Period is a column in the original data):

     

     

    2. Create a disconnected table "Types". Sort the Type column by the Index column. No relationship exists between these two tables.

     

     

    3. Create measures:

     

    Sell-out = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Sell-out" )

Sell-through = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Sell-through" )

Shipment = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Shipment" )

Sell-out vs Shipment = [Sell-out] - [Shipment]

Sell-through vs Shipment = [Sell-through] - [Shipment]

Type Switch =
SWITCH (
    SELECTEDVALUE ( Types[Type] ),
    "Sell-out", [Sell-out],
    "Sell-through", [Sell-through],
    "Shipment", [Shipment],
    "Sell-out vs Shipment", [Sell-out vs Shipment],
    "Sell-through vs Shipment", [Sell-through vs Shipment]
)

     

    4. Create matrix:

     

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    AldoMF,

     

    Try this solution.

     

    1. Structure the data like this (you can use Unpivot in Power Query if each Period is a column in the original data):

     

     

    2. Create a disconnected table "Types". Sort the Type column by the Index column. No relationship exists between these two tables.

     

     

    3. Create measures:

     

    Sell-out = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Sell-out" )

Sell-through = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Sell-through" )

Shipment = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Shipment" )

Sell-out vs Shipment = [Sell-out] - [Shipment]

Sell-through vs Shipment = [Sell-through] - [Shipment]

Type Switch =
SWITCH (
    SELECTEDVALUE ( Types[Type] ),
    "Sell-out", [Sell-out],
    "Sell-through", [Sell-through],
    "Shipment", [Shipment],
    "Sell-out vs Shipment", [Sell-out vs Shipment],
    "Sell-through vs Shipment", [Sell-through vs Shipment]
)

     

    4. Create matrix: