Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Katerina_e
Frequent Visitor

SUMX of a column and a filtered measure

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.

 

Capture.PNG 

Capture1.PNGCapture3.PNG

 

2) Tables are connected by "Product" field.

 

Capture4.PNG

 

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.

 

Capture5.PNG

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.

 

Capture6.PNG

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.

 

1 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.

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

Regards

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.