topic Re: combine groupby with sum in DAX Commands and Tips

combine groupby with sum

Anonymous — Tue, 26 Jul 2022 10:42:29 GMT

hi,

I could realy use your help on this one 🙂

I have two tables, both containing a key wich i could use to match rows of the two tables.

But, this would be a many to many realtion because in both table the key is present more than 1 time.

what i to compute is the total sales amount by key from table A, and then also compute the total sales amount by key of table B.

for table A i have used groupby. But now i would like to add a column in which the sum of sales by key from table B is shown. That way i can compare "Amount_By_Key" with "SAM sum of sales" per "key".

Basically i want to compare the total sales amount by key in table A with table B.

NOTE: some keys that excist in table A dont appear in table B.

please find attached the data: OneDrive data

Re: combine groupby with sum

tamerj1 — Tue, 26 Jul 2022 11:24:36 GMT

Hi Anonymous

Please use

SAM sum of sales = SUMX ( RELATEDTABLE ( 'SAM Facturatieregels' ), 'SAM Facturatieregels'[Overeenkomstnummer] )

Re: combine groupby with sum

Anonymous — Tue, 26 Jul 2022 11:31:27 GMT

hi tamerj1 ,

thanks for the reply. When i try to do that i get an exception:

the key contains numeric values combined with text values
any other suggestion?

Re: combine groupby with sum

tamerj1 — Tue, 26 Jul 2022 11:33:14 GMT

Anonymous
I thought this is the column that you want to sum. What is the name of that column?

Re: combine groupby with sum

Anonymous — Tue, 26 Jul 2022 11:37:49 GMT

tamerj1

i want to sum 'SAM Facturatieregels' [Factuurbedrag in valuta] by key.
when i try to do this i get:

please have a look in the excel file (One drive) tab "result".

that exact outcome i would like to build in PowerBI

Re: combine groupby with sum

tamerj1 — Tue, 26 Jul 2022 11:43:08 GMT

Anonymous
Then use

SAM sum of sales = SUMX ( RELATEDTABLE ( 'SAM Facturatieregels' ), 'SAM Facturatieregels'[Factuurbedrag in valuta] )

Re: combine groupby with sum

Anonymous — Tue, 26 Jul 2022 11:54:33 GMT

already did so (see my reply before this message).
the outcome is not satisfying as you can see.

Re: combine groupby with sum

tamerj1 — Tue, 26 Jul 2022 12:06:49 GMT

Anonymous
Are you sure there is a relationship between the twoo tables? If not you can use

SAM sum of sales = SUMX ( FILTER ( 'SAM Facturatieregels', 'SAM Facturatieregels'[Overeenkomstnummer] = 'CBA by key'[Key] ), 'SAM Facturatieregels'[Factuurbedrag in valuta] )