cancel
Showing results for
Did you mean:
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.   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.

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.

2 REPLIES 2
Highlighted 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. 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.

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### 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

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,342)