Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous ll send you the bill hehe 🙂 just kidding
do you have an email address i can send the file to?
Proud to be a Super User!
@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?
Proud to be a Super User!
@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.
@Anonymous query editor is power query, i have no problem providing the solution only trying to help you understand the product
Proud to be a Super User!
@Anonymous ll send you the bill hehe 🙂 just kidding
do you have an email address i can send the file to?
Proud to be a Super User!
@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
@Anonymous power query is part of power bi, are you saying you can't use power query in power bi? if not why not?
Proud to be a Super User!
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.
@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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |