Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I would like to visualize a line chart with January - december 2017 on x-Axis. I want to visualize actual data of selled amount for the months January and february, for the rest of the months in 2017 I want to use forecast data which I have given in a table by month. As well I want to show the last year values (Jan-Dec 2016) that are given in a seperate table too, but I don't know how to realize it. Is it possible to use the format of the tables for the last year and forecast values? Or do I have to put the forecast data in the format like my actual data so that it can be visualize as one line in the graph?
My actual data table has the form like the following table: I need it to get the Sum of the selled amount to visualize it. Aim is to visualize the selled amount and to be able to filter it by product Category.
Customer ID | Category | Selled Amount | Date | XY | XY |
1 | Sweets | 1 | 01.02.2017 | ||
2 | Vegetables | 2 | 01.03.2017 | ||
3 | Vegetables | 1 | 01.01.2017 | ||
4 | Sweets | 3 | 02.02.2017 | ||
5 | Soup | 1 | 03.02.2017 | ||
6 | fruits | 2 | 02.02.2017 | ||
7 | Soup | 1 | 19.01.2017 | ||
8 | fruits | 2 | 05.01.2017 |
For the forecast data I have a table in the format like that:
Category | Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | Sep | Okt | Nov | Dez |
Sweets | 100 | 120 | 80 | 85 | 96 | 84 | 88 | 100 | 105 | 77 | 88 | 100 |
Vegetables | 50 | 40 | 30 | 25 | 27 | 26 | 23 | 50 | 40 | 36 | 39 | 45 |
fruits | 20 | 13 | 18 | 28 | 30 | 40 | 20 | 10 | 15 | 36 | 25 | 12 |
soup | 30 | 26 | 20 | 16 | 12 | 15 | 19 | 21 | 20 | 30 | 45 | 50 |
And the last year values have the same format like the forecast value table:
Category | Jan 16 | Feb 16 | Mrz 16 | Apr 16 | Mai 16 | Jun 16 | Jul 16 | Aug 16 | Sep 16 | Okt 16 | Nov 16 | Dez 16 |
Sweets | 90 | 180 | 110 | 85 | 100 | 84 | 88 | 71 | 105 | 77 | 75 | 80 |
Vegetables | 49 | 40 | 50 | 80 | 27 | 86 | 80 | 60 | 40 | 36 | 46 | 50 |
fruits | 30 | 73 | 0 | 28 | 100 | 40 | 27 | 30 | 15 | 36 | 25 | 80 |
soup | 10 | 80 | 60 | 50 | 12 | 15 | 40 | 15 | 16 | 35 | 50 | 90 |
I also have a Date table including all dates from 01.01.2016-31.12.2017. I have connected it to the date field of the actual data field.
Can someone help me how I could visualize it? I really don't know how to do it...
Many thanks in advance for any tips!
Best Regards
Solved! Go to Solution.
Hi @mueller_82,
You need to create a date column both in Forecast table and Last Year table. Then create a relationship between the date table with those two tables based on the date column. In Forecast table, create a column to return whether the row is to display in the line chart. Then use this column in Visual level filter pane to filter the line chart. For detail information, see attached .pbix file.
Best Regards,
Qiuyun Yu
Hi @mueller_82
I suggest you change the format of your forecast table (and the last-year forecast table) to something like this
Category , Month , Value --------------------------------- Sweets , Jan , 100 Sweets , Feb ,120 Sweets , Mrz , 80
You can achieve this using the Unpivot feature in the Query Editor.
Once you have the data in this format, it should be easier to build your visual
thank you very much! I now used the univot feature to change the format of the forecast and the last year table as you suggested (Category, Month, Value for selled amount) .
Im really new in Power Bi and I don't know what the best strategy is to visualize the actual data (Jan 17 and Feb 17) and the forecast data (Mrz17-Dez17) in one line. What measure do I need? Is it better to create one table for that by adding the forecast table to the actual data table, even if the actual data table consists of more columns so that some would be blank?
Maybe there is a general procedure how to visualize actual data/forecastdata/Last year data correctly?
Thanks!! 🙂
Hi @mueller_82,
You need to create a date column both in Forecast table and Last Year table. Then create a relationship between the date table with those two tables based on the date column. In Forecast table, create a column to return whether the row is to display in the line chart. Then use this column in Visual level filter pane to filter the line chart. For detail information, see attached .pbix file.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |