Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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
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:
Proud to be a Super User!
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.
@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.
What does the "_1" mean?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |