Tables with different level

[Ranjith_96]

Hi Power BI Community,

Please find my below table structure.

I have two tables: Orders and Marketing table

1. Order table has 4 columns - Date, Country, Channel and Total_Orders

2. Marketing table has 3 columns - Date, Country and Marketing_spend

The output I need is a table which has 4 columns - Date, Country, Total_Orders and Marketing_spend.

Problem is Orders table has Channel column, so order table is at Date, Country and Channel level but the marketing table is at Date and Country level. When I merge the two tables, marketing_spend is duplicated.

How can i get rid  of these issue? How can i join two table which are at different levels?

Note: I also need channel column, bcoz of other visuals in the report.

Any help would be really helpful. Thanks in Advance!!

In Tableau, I can do this by data blending concept.

[Ranjith_96]

Hi @gregoliveira . Thanks much for your response!

could you please look into this pbix which has the two tables and help me in doing the same using DAX.

doubt.pbix.

Thanks for your help.

@v-zhenbw-msft  seeking your help here on this post, Please assist. 

[gregoliveira]

Hi.

You can deal with this situation better using DAX and relations between tables. I will need a calendar table and a country table. After this, you can author some measures to split the marketing spend based, for example, in the total orders.

However, if you need to accomplish this in Power Query, you can first aggregate the Orders table by Date and Country to sum the Orders per Channel and then merge this this table with the Marketing table.

Hope this help you.