combine groupby with sum

[Evandam]

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 

[tamerj1]

Hi @Evandam 

Please use 

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

[Evandam]

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?

[tamerj1]

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

[Evandam]

@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

[tamerj1]

@Evandam 
Then use

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

[Evandam]

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

[tamerj1 · Accepted solution]

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