Reply
fksng66
Advocate I
Advocate I
Partially syndicated - Outbound

Combine two charts into one

Hi there, 

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

fksng66_0-1724295753545.png

 

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?
fksng66_3-1724296278725.png

 


 

 
3 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Irwan
Super User
Super User

Syndicated - Outbound

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

Irwan_0-1724298850390.png

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

Irwan_1-1724298876798.png

Irwan_2-1724298898723.png

 

Hope this will help.

Thank you.

View solution in original post

Syndicated - Outbound

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

View solution in original post

7 REPLIES 7
Irwan
Super User
Super User

Syndicated - Outbound

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

Irwan_0-1724298850390.png

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

Irwan_1-1724298876798.png

Irwan_2-1724298898723.png

 

Hope this will help.

Thank you.

Ashish_Mathur
Super User
Super User

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
anmolmalviya05
Super User
Super User

Syndicated - Outbound

Hi @fksng66, Hope you are doing good!

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

Syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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

fksng66_0-1724307409895.png

 

But now it's amost 200k for paid ads

fksng66_1-1724307409893.png

 

any idea as to why?

 

Syndicated - Outbound

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

 

fksng66_3-1724315152239.png

fksng66_4-1724315205043.png

 

thanks again @anmolmalviya05 @Ashish_Mathur @Irwan 

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)