SUM of coulmns in two different sheets

[fksng66]

I have two different sheets imported and I would like to calculate the total sum value from these two columns

Let's say one of my sheet named Facebook, and another is PaidAd_FB, both contains the impressions column and I want to calculate the sum value of both. 

I tried to use this mesuare formula but doesn't work unfortunately.

SUM(Facebook[Impressions]) + SUM(PaidAd_FB[Impressions])

Did I miss out anything?

[v-rzhou-msft · Accepted solution]

Hi @fksng66 ,

Here I suggest you to add a DimDate table and add a YearMonth column in both tables to help your calculation.

DimDate Table:

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 01, 01 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMM" ),
    "YearMonthSort",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Calculated Column:

In Facebook:

YearMonth = 
COMBINEVALUES("-",Facebook[Year],Facebook[Month])

In PaidAd:

YearMonth = 
COMBINEVALUES("-",PaidAd[Year],PaidAd[Month])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  The Territory slicer should be dragged from a seperate Dim Table which should in turn be connect to the FB and PaidAd_FB tables.

[rajendraongole1]

Hi @fksng66 The DAX formula you provided is correct for summing the values from the two columns in different tables, you can check on data types, column name that is reference form both tables along with relationship with right Facebook and PaidAd_FB or with other tables.

if there is any blanks , you can try below measure with colease function

Total Impressions =
SUMX(
Facebook,
COALESCE(Facebook[Impressions], 0)
) +
SUMX(
PaidAd_FB,
COALESCE(PaidAd_FB[Impressions], 0)
)

Hope this helps.

[fksng66]

hi @rajendraongole1 thanks for the reply. i've tried it but unfortunately doesn't seems to work as well.

i took the impressions value from the facebook and paidAd_fb and the total sum i got was supposed to be 90402 for NSEA on Jan month, but on my power bi chart it got more than 1.2m for just jan alone

this is the data from the source file 

Facebook

PaidAd_FB

[v-rzhou-msft · Accepted solution]

Hi @fksng66 ,

Here I suggest you to add a DimDate table and add a YearMonth column in both tables to help your calculation.

DimDate Table:

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 01, 01 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMM" ),
    "YearMonthSort",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Calculated Column:

In Facebook:

YearMonth = 
COMBINEVALUES("-",Facebook[Year],Facebook[Month])

In PaidAd:

YearMonth = 
COMBINEVALUES("-",PaidAd[Year],PaidAd[Month])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fksng66]

@v-rzhou-msft I tried to replicate what you did there, but i got a very different kind of result tho

Couldn't seem to find where I'm missing out

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[fksng66]

Hi @Ashish_Mathur here's the link. i've uploaded it to google drive

https://drive.google.com/file/d/1k5G4Xl4gCAnmcuO4MY9M4VWbAo1N2fj9/view?usp=drive_link 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  The Territory slicer should be dragged from a seperate Dim Table which should in turn be connect to the FB and PaidAd_FB tables.

[fksng66]

@Ashish_Mathur Hi thanks for this, but the data for paid ads seems to be combining all the territories together for Jan month now, supposedly for NSEA Jan month the sum is 75447

But now it's amost 200k for paid ads

any idea as to why?

I tried to recreate one myself, I also noticed the icon for the date column is different from yours, not sure why is that so?

[fksng66 · Accepted solution]

okay it worked now, i've the territory (paidAd_fb) connected to territory (facebook) to form a many-to-many relationship and it's able to show the correct value for both organic and paid ads now

thanks again @Ashish_Mathur @v-rzhou-msft for the help!