cancel
Showing results for
Did you mean:
Frequent Visitor

## Summing on multiple rows

Hi,

I am trying to create a measure that will sum my values based on two rows. My current formula is:

SUMX(VALUES(Table[customer]),Table[Price Variance])

What i actually need is for the measure to sum the price variance based on the product within each customer. If I put produt in the SUMX formula then it disregards the customer and if i put customer then it doesn't take into account the product. Is there a way to tell it to consider both?

Below is kind of what i am getting. The product numbers are right but then it summarizes it on customer so that one is wrong. I need it to say \$20.

Customer Name     \$30

Product A         \$10

Product B         \$10

Hope this makes sense.

1 ACCEPTED SOLUTION
Super User

@chenko90 , This only need when Price Variance is measure

SUMX(summarize(Table, Table[Product], Table[customer]), "_1", Table[Price Variance]), [_1])

when price variance is a column

SUMX(summarize(Table, Table[Product], Table[customer], Table[Price Variance]), [Price Variance])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
5 REPLIES 5
Community Support

Hi  @chenko90 ,

I created some data:

Here are the steps you can follow：

Method one

Create measure.

``````method one =
var _1=SUMMARIZE('Table','Table'[MAJOR_NAME],'Table'[SKU],'Table'[Price Vanriance])
return
SUMX(_1,[Price Vanriance])``````

Result:

Second method

Create measure.

``````second method_1 =
IF(ISINSCOPE('Table'[MAJOR_NAME])&& NOT(ISINSCOPE('Table'[SKU])),SUMX('Table',[Price Vanriance]),MAX('Table'[Price Vanriance]))``````
``````second method_2 =
var _table=SUMMARIZE('Table','Table'[MAJOR_NAME],"_value",[second method_1])
return
IF(HASONEVALUE('Table'[MAJOR_NAME]),[second method_1],SUMX( _table,[_value]))``````

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Super User

Hi,

Typically a simple SUM should work in these kinds of cases, but I assume there is some issue with using it. Try adding DISTINCT before your VALUES so something like this:

DISTINCT(VALUES(Table[customer]). It is somewhat difficult to understand what is going on here. Do you have some sort of sample data?

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Frequent Visitor

Here is a visual of what im working with. As you can see the total for customer A is wrong. The sku level numbers are correct. When i use the SUM formula it has the same issue where it runs the formula in the measure instead of summing the sku level numbers.

Super User

@chenko90 , This only need when Price Variance is measure

SUMX(summarize(Table, Table[Product], Table[customer]), "_1", Table[Price Variance]), [_1])

when price variance is a column

SUMX(summarize(Table, Table[Product], Table[customer], Table[Price Variance]), [Price Variance])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

What does the "_1" mean?

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors