Not quite understanding Totals In Matrices

[Anonymous]

Hello my wonderful Power BI Community,

I've run into a bit of an issue I'm hoping you all can share some light on. I've looked everywhere but have failed to find anything that is remotely successful.

I'm currently working on a pricing report. The report utilizes a singular fact table of invoices stored in our SQL database which is queried by PBI twice for different results. The first query is called "Base Period" which is anything with an invoice date < January 01 of current year. The other table is called "Current Period" which is anything >= January 01 of current year. I then join these two tables to a bunch of Lookup tables like customers, items, etc. on their associated keys to get various fields. For example, LU Customer joins to both base period and current period on Customer_Key. LU Item joins on Item_Key, etc. Notably I have a fiscal calendar table joined ONLY to the current period table since I want people to be able to slice invoices in the current year by period number, but still see the average price of the base period as a full year calculation. See structure below:

I've then created a matrix with Company, Customer, Product Category, and Item Code in the rows. I've then created a bunch of measures to calculate various fields. I'll provide the general idea along with the measure text in red or a screenshot. Further below is a screenshot of the created matrix so you can follow along.
- Average selling price (ASP) in the base period --> Average Selling Price Base Period = average('Base Period'[LocalUnitPrice])

- Average selling price (ASP) in the current period --> Average Selling Price Current Period = average('Current Period'[LocalUnitPrice])
- Price Traction P1 is the first part of the price traction $ equation. It takes the Current Period ASP and subtracts the base period ASP --> Price Traction P1 = [Average Selling Price Current Period] - [Average Selling Price Base Period]

- Total quantity is simply the sum of the quantity invoiced from the current period --> Total Quantity = sum('Current Period'[QuantityInvoiced])

 - Price traction $ is the Price Traction P1 multiplied by the total quantity. I've thrown in an IF to make sure that the price traction $ is 0 if either of the ASPs are blank (code in sceenshot below). You'll see further down in the screenshot of the matrix that at the item code level (1901x, 894520, etc.), this calculates correctly. However, the totals need to be the sum of these values rather than the total line P1 * total quantity. This is where the price traction fix below comes into play.
 

- Price Traction Fix is my culmination of hours of research and trying all kinds of things, however it still doesn't work. The equation simply does a sumx based on my LU_Item[Item Code]  and the price traction $ measure (see code below). This was ripped straight from an article I read and has gotten me closest to what I was trying to achieve.

 
Now the problem arises when you look at the bottom screenshot:

We can see the faulty Price Traction $ not doing what I want it to do. The price traction fix however seems to fix it until i go to the upper most total where I'm $246.53 off. If I sum 1901x and 894520 for each customer (Grainger and Airgas) I get the correct amounts, but then those amounts don't sum correctly to the company level at the top.

The only other filters i have applied are:

A base period year which limits the base period table to only show me the prices I want to compare to
A customer filter to limit the data I'm seeing in my visual
A company filter to limit the data I'm seeing in my visual

I'd appreciate any help anyone is willing to give! Thank you for time and energy! Have a blessed day.

Sincerely,
Nick

[Anonymous · Accepted solution]

Solved the issue after realizing how the data was interacting with my architecture. I needed to change the fix measure to this:

The key to figuring this out was to understand that if you put a fact table in the table part of the summarize function, you can use columns from all tables connected to it to summarize on. I thought I couldn't use summarize because I needed to choose the table that the columns i needed were actually in which was impossible because I had two columns in separate tables.

I hope this helps someone else!

[Anonymous]

So I've figured out the issue. When SumX'ing by item, the values are correct. However, when customer is added, it breaks. It breaks because customers can have different pricing structures which means that their ASPs will be higher or lower which means the Price Traction P1 will be larger or smaller leading to bigger/smaller numbers which don't sum correctly.

I now need to figure out how to sumx with multiple columns to try and get it to sumx based on a combination of Customer Group & item code