cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Frequent Visitor

Re: SUMX of a column and a filtered measure

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
Highlighted
Microsoft
Microsoft

Re: SUMX of a column and a filtered measure

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

Highlighted
Frequent Visitor

Re: SUMX of a column and a filtered measure

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

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors