Summing on multiple rows

[chenko90]

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.

[v-yangliu-msft]

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

[ValtteriN]

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!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[chenko90]

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. 

[amitchandak · Accepted solution]

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

[chenko90]

What does the "_1" mean?