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.
Happy Wednesday,
Can you please suggest for below scenario?
I want to sum sales amount and invoice if “ADVICE” and “KEY” are same,I tried if condition but not able to figure it out the result.
Data Set |
|
| ||||||||
Item | Advice | Key | Sales Amount | Invoice |
| |||||
A | Include | abc-123 | 10 | 2 |
| |||||
A | Include | abc-123 | 20 | 3 |
| |||||
A | Dealer | abc-123 | 30 | 4 |
| |||||
A | Dealer | abc-123 | 40 | 5 |
| |||||
Expected Result | ||||||||||
Item | Advice | Key | Sales Amount | Invoice | Price | |||||
A | Include | abc-123 | 30 | 5 | 6 | |||||
A | Dealer | abc-123 | 70 | 9 | 7.777777778 | |||||
Thanks
Solved! Go to Solution.
Sure. You've got a slight mistake in how you're setting up the query. Remember, FILTER returns a table, so use that for the table you're summing across in your SUMX. I just replaced the first '2018 RAW Data' term with your FILTER term:
SalesTotal = SUMX(FILTER('2018 RAW Data', '2018 RAW Data'[advice] = SELECTEDVALUE('2018 RAW Data'[advice]) && '2018 RAW Data'[Key] = SELECTEDVALUE('2018 RAW Data','2018 RAW Data'[Key])), '2018 RAW Data'[Sales Amount])
I think there are other issues (like you name a table then a column in your second SELECTEDVALUE when you only need the column), but this should get you started.
This should do the trick:
SalesTotal = SUMX(Table[Sales Amount],FILTER(Table, Table[Advice] = SELECTEDVALUE(Table[Advice]) && Table[Key] = SELECTEDVALUE(Table[Key]) ))
Note that this will only evaluate when you're in a context where you only have 1 Advice and 1 Key, which will happen automatically when putting this into a table. If you're trying to use this in future calculations, they all need to have the data set filtered to having only one advice and key, or this will return a blank. You can also use MAX or FIRSTNONBLANK instead of SELECTEDVALUE if you want to always return something.
Set up a measure for each column you're summing this way, and you're good to go.
Hi,
Thank you so much for your suggestion.
Please see below my formula is giving me an error,Can any one suggest?
ERROR:"Too many arguments were passed to the SUMX function. The maximum argument count for the function is 2."
SalesTotal = SUMX('2018 RAW Data','2018 RAW Data'[Sales Amount],
FILTER('2018 RAW Data','2018 RAW Data'[advice]=
SELECTEDVALUE('2018 RAW Data'[advice]) && '2018 RAW Data'[Key]=
SELECTEDVALUE('2018 RAW Data','2018 RAW Data'[Key])))
Sure. You've got a slight mistake in how you're setting up the query. Remember, FILTER returns a table, so use that for the table you're summing across in your SUMX. I just replaced the first '2018 RAW Data' term with your FILTER term:
SalesTotal = SUMX(FILTER('2018 RAW Data', '2018 RAW Data'[advice] = SELECTEDVALUE('2018 RAW Data'[advice]) && '2018 RAW Data'[Key] = SELECTEDVALUE('2018 RAW Data','2018 RAW Data'[Key])), '2018 RAW Data'[Sales Amount])
I think there are other issues (like you name a table then a column in your second SELECTEDVALUE when you only need the column), but this should get you started.
Thank you so much for your Help and Explaniation.
Merge those two columns together and put that new column as the row option in a matrix?
Hi,
Thank you so much for your suggestion.
Can i do it without Matrix Visulization?
I need to use result for further calculations.
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |