Sum X But with data from two tables - Am I doing something wrong?

[NickAdmin]

I'm working on a pricing analysis for my company which consists of two sets of invoice fact data joined to by multiple dimension tables. The goal is to be able to select a base year + base period (BP) and a compare/current year + compare/current period (CP) and see our price traction, volume traction, etc. We compare pricing etc. on a customer + item SKU level because different customers can have different pricing models. 

My CEO requested a small change to the volume traction calculation that is throwing me for a loop. The original scope of the report is that we would only compare Base to current if a match existed. Meaning if a customer bought something in the base period, but then didn't buy it in the current period, we would exclude those records. Because of this I was able to do some simple SumX calculations only looking at the current period like this:

Price Traction p1 =

IF(

    and(

        'Base Period'[Average Selling Price Base Period] > 0

        ,'Current Period'[Average Selling Price Current Period] > 0

    ),

        ([Average Selling Price Current Period] - [Average Selling Price Base Period]) * [Total Quantity CP]

    , 0

)

And the subseqent equation:

Price Traction $ =

VAR Result =

    SUMX(

        SUMMARIZE(

            'Current Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), [Price Traction p1]

    )

RETURN Result

However, when looking at some volume metrics, my CEO wanted to see data from both periods. Meaning, if something was sold in the base period but not sold in the current period, he'd want the base period total sales as a negative (because we lost that business). Likewise if there was nothing in the base period, but there were sales in the current period, he wanted those current period sales to show up because we have gained sales. 

I was hoping I could do an If statement and look for where current period sales were blank and then just do a sumx on the base period. Something akin to this:

Volume Traction =

SWITCH(

    TRUE(),

    and('Current Period'[Total USD Sales CP] = 0, 'Base Period'[Total USD Sales BP] >= 1), 'Base Period'[Total USD Sales BP]*-1,

    and('Base Period'[Total USD Sales BP] = 0, 'Current Period'[Total USD Sales CP] >= 1), 'Current Period'[Total USD Sales CP],

    ([Total Quantity CP] - [Total Quantity BP]) * [Average Selling Price Base Period]

)

And the following equation

Volume Traction $ =

If(

    [Total USD Sales CP] > 0,

        SUMX(

        SUMMARIZE(

            'Current Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), [Volume Traction]

    ),

        SUMX(

        SUMMARIZE(

            'Base Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), [Volume Traction]

    ))

You can see from the screenshot below that at the row level - customer + item code level - everything is working fine, but the totals should read 536k not the various totals I'm getting. The Volume Traction $ is doing the equivalent of a SUMX on the current period regardless of the fact that I've put the other sum X in there.

Volume traction V2 is just my attempt to put everything from Volume Traction + Volume traction $ into one step:

Volume traction V2 =

SWITCH(

    TRUE(),

    and('Current Period'[Total Quantity CP] = 0, 'Base Period'[Total Quantity BP] >= 1),

        sumx(

            SUMMARIZE(

            'Base Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), 'Base Period'[Total USD Sales BP]*-1),

    and('Base Period'[Total Quantity BP] = 0, 'Current Period'[Total Quantity CP] >= 1),

        sumx(

            SUMMARIZE(

            'Current Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), 'Current Period'[Total USD Sales CP]),

       sumx(

            SUMMARIZE(

            'Current Period',

            View_LU_Item[Item Code],

            View_LU_Customer[Customer Group]

        ), ([Total Quantity CP] - [Total Quantity BP]) * [Average Selling Price Base Period]

))

Below is a screenshot of the model. Everything is connected to each other except the base period and the current period tables which are invoice data.  We'd like to keep all the functionality of looking at line level information if possible - being able to break down by currencies, companies, etc. which makes me think I can't summarize the data.

Any help would be greatly appreciated! This is the first time I've run into something like this and it's driving me nuts!

[NickAdmin · Accepted solution]

So I've figured out the solution. SUMX doesn't like to be in an if statement. I assumed the SumX's would work together to give me what I want but I was incorrect. In order to rectify what was happening, I needed to make a measure specifically looking at the base period sales that weren't being included. 

In the end I had to do this:
Equation 1 I didn't update from last time:

Equation 2 breaks out the base period values from equation 1 into its own measure:

And equation 3 adds together equation 1 + equation 2 to give me the proper totals and line level information

[AmiraBedh]

Can you please share your pbix file ?

[NickAdmin]

Volume Traction Issue.xlsx

I've created a one drive link to the file.

[NickAdmin]

Unfortunately I'm unable to since it contains sensitive information. I can however attach an excel file with some sample data, the visual, the equations, and more descriptions of the issue i'm running into. What would be the best way to do that?

[AmiraBedh]

Maybe it is better to share the pbix file containing the model with sample data.