Forum Discussion

TSI's avatar
TSI
Icon for Advocate I rankAdvocate I
6 years ago
Solved

SUM for 2 tables

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 : mon...
sum
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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

TSI's avatar
TSI
Icon for Advocate I rankAdvocate I
6 years ago

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.