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

[Anonymous]

Dear Power BI users, 

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. 

1. Sales Forecast vs. Months

2. Actual Sales vs. Months

On my X-axis is months. and Y-Axis is the value which is Sales Forecast or Actual Sales.

Do you have any ideas how I can achieve this? One idea I have is to change the table structure I have.  To explain, I will start with showing my table. Below are the columns that I have in my table.

Month  |  Sales Forecast | Actual Sales | Budget

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.

Month | Type | Value | Budget

May  | Sales Forecast | 1000 | 2000

May | Actual Sale s | 900 | 2000

June | Sales Forecast | 1200 | 2200 

June | Actual Sales | 1100 | 2200

Looking forward for your replies 🙂

Sincere Regards, 

Sai Srinivas

[Anonymous]

I would reccomend creating a measure that will display the values you want depending on a slicer

To do that, follow these steps:

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:

Measure ID         Measure Name

     1                      Forecasted Sales

     2                      Actual Sales

2) 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

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"

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:         Measure Selection = Min(MeasureSelectionTable'[Measure ID])

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:

        Measure For Visual = Switch('MeasureSelectionTable'[Measure Name], 

                                          1, 'Your Forecasted Sales Measure',

                                          2, 'Your Actual Sales Measure')

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!

I hope this helps!