Sum by Group

[Anonymous]

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

[Cmcmahan]

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.

[Anonymous]

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])))

[Cmcmahan · Accepted 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.

[Anonymous]

Thank you so much for your Help and Explaniation.

[jthomson]

Merge those two columns together and put that new column as the row option in a matrix?

[Anonymous]

Hi,

Thank you so much for your suggestion.

Can i do it without Matrix Visulization?

I need to use result for further calculations.

Thanks