Forum Discussion
Dynamic Multiple Line Chart based on slicer
Hi All,
I'm gently shifting from Tableau to PowerBi and hence I'm in that painful moment in which I'm recreating all the reports on this awesome new tool.
One thing in which I'm stuck is the creation of a dynamic multiple line chart based on the values of a slicer.
In details
I need to create a line chart that shows below lines based on the selected year
- Selected Year Actuals
- Selected Year Budget
- Selected Year -1 Actuals
A few constraints I have:
- The Year Column is a whole number (2019, 2018, 2017, etc)
- I have a month column in which months are expressed as text (January, February, March,...)
- I have one column with Actual Values
- I have one column wiht Budget Values
- I have a column for Data Type (Budget, Actual)
I've tried multiple solution using different date functions. They don't work properly as I don't have a proper date column. But probably I'm missing somehting in my newbietude
In Tableau this was solved this way (ndr I know they are two very different tools but it's to get the idea):
1) I've created a year parameter with different year values to be used as slicer (matching the year column values)
2) Create one Measure adding up Actual and Budget
3) Create two select measures. One selects the sum of Actual and Budget where year = parameter selection. The second selects only Actual values where year = parameter -1.
4) drop the two measures into a line chart
5) use year and data type as legend (eg, if 2019 is selected we will have 2019 Actuals, 2019 Budget, 2018 Actuals)
I've used the search function to look for a similar problem but I didn't find any. But, if I've overlooked something similar I will be happy to close this down
Thanks in advance for any help!
Zen
Edit: Link to sample table:
https://docs.google.com/spreadsheets/d/1j-obrgTNnDWWcAXY0khzblO11-7whCED22P0h1E3llk/edit?usp=sharing
Hi AndrejZitnay ,
Thanks for your reply,
Unfortunately this method is not sustainable in the longrun (creating a new graph each time a new year starts)
I've just found out an easier way to do so which is fully automated and does require very little maintenance:
Step 1 - Create a new table (name Table) with year as text and their equivalent as first of January (eg, 2019 = 01/01/2019, 2018 = 01/01/2018)
Step 2 - Link the two table using the text year columns
Step 3 - Create a measure just for Previous Year = CALCULATE(([Actuals]),PREVIOUSYEAR('Table'[Date]))
Step 4 - Drop the three measures in one graph
Step 5 - Create a slicer using the Dates from the new "Table" and set the formats as yyyy
Only maintenace to do is to add new year in the table as both text and date. I've already added until 2028. If the user selects a date with no data it will blank the graph forcing her/him to select another date.
Even if I've found a solution myself thanks for providing some answer that managed to order my thoughts
Best,
Davide
6 Replies
Hello Zennificator
I would recommend to you to spend bit of time on guided learning.
https://docs.microsoft.com/en-us/power-bi/guided-learning/
It helped me a lot at beginning of my journey with Power BI.
(It is really beneficiary to go step by step)
Everything what you want is possible but difficult without seeing your data.
Andrej
Hi Andrej
Thanks for your reply, I've added a link to a sample table
Zen
Hello Zennificator
1 I recommend to Merge Values into one column and change it to Values.
You can do that in edit queries
2 Create basic table which will help to give order to Months
3 Create relationship between tables
4 Sort your table by Order
5 Create you chart (Month from Table in Axis)
Type in Legend
Values in Value
Year should be in separate slicer
That's how I work with my actuals and budgets.
