Combine two charts into one

[fksng66]

Hi there, 

I would like to combine these two charts (organic + paid ads) into one bar chart

I tried to create a measure with the fomula 

Total Impressions (organic+paid ads) = SUM(Facebook[Impressions]) + SUM(PaidAd_FB[Impressions])

but this is the result i got instead, did I missed out something here?

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and single) from the Date column of both Fact Tables to the Date column of the Calendar Table.  To the Xaxis, drag Year and Month name from t he Calendar Table.

Hope this helps.

[Irwan · Accepted solution]

hello @fksng66 

have you set a relationship (month column) between those two tables?

I made a simple example below with exact same DAX as you used :

- without relationship - the result is sum-ed to all month

- with relationship (month column) - the result is depend on month relationship

Hope this will help.

Thank you.

[Irwan · Accepted solution]

hello @fksng66 

have you set a relationship (month column) between those two tables?

I made a simple example below with exact same DAX as you used :

- without relationship - the result is sum-ed to all month

- with relationship (month column) - the result is depend on month relationship

Hope this will help.

Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and single) from the Date column of both Fact Tables to the Date column of the Calendar Table.  To the Xaxis, drag Year and Month name from t he Calendar Table.

Hope this helps.

[anmolmalviya05]

Hi @fksng66, Hope you are doing good!

Do you want to show different bars for the scenario in a single visual?

[fksng66]

@anmolmalviya05 supposedly i should get 90402 for jan's month data after combining both the organic and paid ads

[anmolmalviya05 · Accepted solution]

@fksng66 Okay, So basically you can use a date table and connect it to both the tables from where the data is coming, and use the x-axis columns from the date table, then your data will get filter according to your requirement.

To create a date table you can use below dax:

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date])
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[fksng66]

Hi @anmolmalviya05 

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?

[fksng66]

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 @anmolmalviya05 @Ashish_Mathur @Irwan