Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |