Forum Discussion
Sum by Group
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.