topic SUM for 2 tables in DAX Commands and Tips

SUM for 2 tables

TSI — Sat, 30 Nov 2019 03:36:42 GMT

Hi there,

I'm a newbie and trying to sum up Headcount from 2 different tables:

1. Country Main : this is the main database that contains headcount for all countries, by month

2. Country A : monthly headcount data specific to Country A

and getting to a monthly Total Headcount.

I've tried to create a measure: Total Headcount, but it's not adding up correctly - headcount for all countries are inflated by Country A's Total:

I can't append the tables and need to use DAX. Could somone give me a hand here?

Please find PBI file here.

Many thanks!

Re: SUM for 2 tables

az38 — Sat, 30 Nov 2019 08:11:36 GMT

hi TSI

try this

Total Headcount = sumx('Country Main';'Country Main'[Headcount]) + lookupvalue('Country A Monthly'[Headcount];'Country A Monthly'[Month];selectedvalue('Country Main'[Month]);'Country A Monthly'[Country];SELECTEDVALUE('Country Main'[Country]) )

But, it won't calulate total of 'Total Headcount' correctly

in my opinion, for your data model would be better to create key columns in both tables, like this for 'Country Main'

ColumnKey = concatenate('Country Main'[Country];'Country Main'[Month])

and for 'Country A Monthly'

ColumnKey = concatenate('Country A Monthly'[Country];'Country A Monthly'[Month])

then create one-to-one relationships between tables via ColumnKey

and then in Country Main table create new column

Column = 'Country Main'[Headcount]+related('Country A Monthly'[Headcount])

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

Re: SUM for 2 tables

TSI — Sat, 30 Nov 2019 11:00:19 GMT

Hi az38 ,

The sum by Market worked!

The extra solution you gave of creating a key column in both tables is brilliant - you went the extra mile after noticing that the Total didn't add up correctly even when sum by Market did. Being new to DAX, I really appreciated the detailed steps and clear explanation.

Thank you so much for your help.