How to visualize 2 different sets of data in 1 single bar chart ?

[Anonymous]

I have 2 different sets of data, both could not be related with any unique relationship or ID

i) table of raw data with stacks of collumns and rows

ii) budget table with combined and aggregated data for Year 2018

Example:

ii)  table of raw data with stacks of collumns and rows

Country	Actuals	Fiscal Quarter	FISCAL YEAR
Japan	12	Q4	2017
China	15	Q4	2016
Malaysia	12	Q4	2016
Thailand	13	Q4	2016
New Zealand	6	Q1	2017
Indonesia	8	Q4	2016
China	10	Q4	2019
Japan	5	Q3	2018
Vietnam	2	Q3	2018
Australia	1	Q3	2018
China	3	Q1	2018
Hong Kong	25	Q4	2018
China	15	Q4	2019
Macau	22	Q2	2016
Singapore	26	Q3	2017
Taiwan	32	Q3	2017

ii) budget table with combined and aggregated data for Year 2018

Region	Q1	Q2	Q3	Q4
Greater China	12	22	32	5
Japan	11	17	16	8
ANZ	13	30	22	19
SE ASIA	15	45	39	27

In Power BI, how do I visualize the above 2 tables into 1 single stack bar chart showing actuals and budget group by region?

The only solution I can think of is to visualized the data in scatter chart and then export the "see records" into CSV files and then combine the exported files with ii) budget table with combined and aggregated data for Year 2018.

However, this approach is manual and not live.

Appreciate better advise.

Many Thanks

[vanessafvg]

@Anonymous i would transform the data in power query , the only thing is your naming for the regions is different, 

is country and region the same or do you have a look up table that brings them together?  also are your budgets all to do with 2018?

[Anonymous]

@vanessafvg I don't quite get you. Even after google, no results are showing using Power Query in Power BI.

I appreicate if you can share the most exact link.

Unless you mean query editor and DAX code is part of Power Query.

Please take note I will need the 2 tables to be related and filter to be worked.

If you find difficulty in providing solution let other Microsoft Power BI expert to support this case.

[vanessafvg]

@Anonymous query editor is power query, i have no problem providing the solution only trying to help you understand the product

[Anonymous]

@vanessafvg let me know once you have the solution with Query Editor 

[vanessafvg · Accepted solution]

@Anonymous ll send you the bill hehe 🙂 just kidding

do you have an email address i can send the file to? 

[Anonymous]

@vanessafvg thank you for your quick reply.

The challenge is to showcase ii) budget table with combined and aggregated data for Year 2018 in Power BI together with i) table of raw data with stacks of collumns and rows.

No Power Query or Excel pivot table is allowed.

Below is the lookup table

ANZ - Australia & New Zealand

SEA - Malaysia, Singapore, Thailand, Indonesia, Vietnam

China - Hong Kong, China, Macau, Taiwan
Japan - Japan

Yes. Budgets is plainly for Year 2018

I will need all sample solutions in Power BI 

[vanessafvg]

@Anonymous power query is part of power bi, are you saying you can't use power query in power bi?  if not why not?

[Anonymous]

@vanessafvg

i)  table of raw data with stacks of collumns and rows - source file is from Salesforce database

ii) budget table with combined and aggregated data for Year 2018 - source file is from Excel

Running Power Query is an Excel add in

If you insist on running Power Query please advise how would you solve it.

Do take note the dashboard and visualization must be shown in Power BI and not in Excel interface.

Anytime if i)  table of raw data with stacks of collumns and rows is refreshed it should be reflected automatically.

[vanessafvg]

@Anonymous as i said power query is part of power bi, it is not a seperate excel add in, please google this as this is power bi 101 sort of thing, it makes up part of the power bi software