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 : 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!

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

2 Replies

  • 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.