Forum Discussion
Sum X But with data from two tables - Am I doing something wrong?
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:
And the subseqent equation:
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 V2 is just my attempt to put everything from Volume Traction + Volume traction $ into one step:
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!
- Anonymous2 years ago
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
5 Replies
- AmiraBedh
Super User
Can you please share your pbix file ?
- AnonymousNot applicable
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
Super User
Maybe it is better to share the pbix file containing the model with sample data.
- AnonymousNot applicable
Volume Traction Issue.xlsx
I've created a one drive link to the file.
- AnonymousNot applicable
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