Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |