Forum Discussion
Sum by Group
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
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.
6 Replies
Merge those two columns together and put that new column as the row option in a matrix?
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.
