SUMX of a column and a filtered measure

[Katerina_e]

Hello! 

I am trying to do something complex, but I will try to explain on a simplified example. I have managed to calculate a lot things and my issue sits in the very last operation here (do not hesitate to start reading from the bottom).

1) Data source

I have 3 tables: Customer data, Price list and Model sets. Customer data has information about the real purchases made by Customers. Price list table has fixed prices (maximum prices), but in the reality Customers get products with discounts/

 Model sets table contains 2 standard product packages. I want to model the total price for each package based on the discounts for categories.

 

2) Tables are connected by "Product" field.

3) Calculations I make in between

Price list price = Customer_data[ Qty (kg)] * AVERAGE(Price_list[Price for 1 kg])

Discount = (SUM(Customer_data[Price list price]) - SUM(Customer_data[Total paid])) / SUM(Customer_data[Price list price])

Discount for category = CALCULATE([Discount]; ALL(Model_sets[Model set];Model_sets[Product]))

Model price per kg = AVERAGE(Price_list[Price for 1 kg]) * (1 - [Discount for category])

Model price for quantity = Model_sets[Model price per kg] * SUM(Model_sets[Qty (kg)])

So I get this table, where I get everything as I want.

Now the problem comes. I want to calculate the total price for a Model set #1 and #2 for each customer. So basically I need SUMX of the last column.

I get this table, which is wrong. The price for Set # 1 for Ivan, let's say, should be (4,93 + 8,21) = 13,14, not 7,33.

I've been playing around with SUMX, SUMMARIZE, CALCULATE, filtering. But I assume the problem somewhere in a fact I use filtered "Discount for category". Do you have any idea how to approach this?

Thanks.

[v-ljerr-msft]

Hi @Katerina_e,

Have you tried the formal below to see it it works?

measure = SUMX ( 'Price_list', [Model price for quantity] )

If it still doesn't work, could you share a sample pbix file which can reproduce the issue? So that I can doing a few tests with it and hopefully figure it out. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Katerina_e · Accepted solution]

Thanks!

I actually just recently solved it somehow. And indeed, SUMX was a right solution here.

I  made some changes like that.

Model price per kg = SUMX(Model_sets; AVERAGE(Price_list[Price for 1 kg]) * (1 - [Discount for category]))

Model price for quantity = SUMX(Model_sets; Model_sets[Qty (kg)] * [Model price per kg])

I also improved the discount for category using the combination of ALL and VALUES functions.

Altogether it somehow solved and now it works just fine. But thanks, though! The solution was near.