Dynamically change Y axis based on input from drop down or list select

Question

Dear Power BI users,&nbsp;&nbsp;I would like to change the Y-axis in the Line and Clustered Column chart in Power BI Desktop dynamically. Now, I have two plots.&nbsp;&nbsp;1. Sales Forecast vs. Months2. Actual Sales vs. Months&nbsp;On my X-axis is months. and Y-Axis is the value which is Sales Forecast or Actual Sales.&nbsp;Do you have any ideas how I can achieve this? One idea I have is to change the table structure I have.&nbsp; To explain, I will start with showing my table. Below are the columns that I have in my table.&nbsp;Month&nbsp; |&nbsp; Sales Forecast | Actual Sales | Budget&nbsp;&nbsp;Do I have to consolidate Sales Forecast column and Actual Sales column under one column like the one below? In this way I believe I can achieve what I wanted. But some other solutions within Power BI without distrubing the table structure would be great.&nbsp;Month | Type | Value | BudgetMay&nbsp; | Sales Forecast | 1000 | 2000May | Actual Sale s | 900 | 2000June | Sales Forecast | 1200 | 2200&nbsp;June | Actual Sales | 1100 | 2200&nbsp;Looking forward for your replies :-)&nbsp;Sincere Regards,&nbsp;Sai Srinivas

anonymous · Answer

I would reccomend creating a measure that will display the values you want depending on a slicer&nbsp;To do that, follow these steps:&nbsp;1) Create a new table by selecting "Enter Data" and use the data pictured below. I would recommend calling the table "MeasureSelectionTable". The table should look something like this:&nbsp;Measure ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Measure Name&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Forecasted Sales&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Actual Sales2) You do not have to create a relationship for this table, it is called a "disconnected table" and should not be linked to any other tables&nbsp;3) Add a slicer using the table you just created, in this case the slicer will only have two values, "Sales Forecast" and "Actual Sales"&nbsp;4) We need to create a measure that works with the slicer we just created. To do this we will use the "Min" function. Use this measure as an example:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Measure Selection = Min(MeasureSelectionTable'[Measure ID])&nbsp;5) The last step will be creating the measure that will change based on whether you select "Sales Forecast" or "Actual Sales" on the slicer. We will utilize the switch function to make this possible! The switch function will toggle from Forecasted to Actual Sales depending on the value selected in the measure! The code for this measure should look something like this:&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Measure For Visual = Switch('MeasureSelectionTable'[Measure Name],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1, 'Your Forecasted Sales Measure',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2, 'Your Actual Sales Measure')&nbsp;6) The last step is placing your new measure into the visual! Using the slicer placed in Step 3, the data in the chart will changed based on the selection!&nbsp;I hope this helps!

Forum Discussion

Dynamically change Y axis based on input from drop down or list select

Related content

Dynamic Status Change for events based on selected date range

Dynamic change of Default Slicer selection

Dynamic images based on selections

Change in category selection changes selected subcategory in slicers

Dynamic Filtering based on date selection