cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

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!

Did I answer your question? Mark my post as a solution!

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.

Frequent Visitor

What does the "_1" mean?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors