Hello all - it's officially week 7 for DS50 and an interesting skill I've learnt is parameter-driven colouring for charts - I find it really fun to allow the user to interact this way, so lets have a look at how to do this. For those that don't know, parameters are fields that can be changed by the user interacting with it.
With this, parameter-driven colouring means I want certain aspects of my chart to change colour depending on their relationship with a parameter - and when the user wishes to change the parameter, I want the colour to be updated. For example, if the parameter created was a target sales value, and the sales data was less than the target, I want the chart to show a colour to represent that the value is below the target. Alternatively, if the sales value is above the target, I want the chart to be a different colour, so the user can know just by glancing that their sales are doing okay. The beauty of this is that - if the user then wants to change their target value - the chart is updated accordingly.
So, to bring this scenario to life, let's look at how to implement this in Power BI.
The dataset I will be using is the Sample - Superstore data.
Parameter-driven colouring in Power BI
- Creating the Parameter
Under the "Modelling" tab, select "New Parameter". In the drop down you can select whether you want your parameter to be a numeric value or based on a pre-existing field in your dataset (these can be date/time, or string fields this way) - note this can be changed in the following pop-up after selection so don't worry if you misclick here. In this scenario, I want to create a Sales Target, so this will be a numeric field consisting of whole numbers ranging from 0 to 350,000 in increments of 100. At the bottom of this menu there is a small tick box that has been pre-selected which will automatically create a slicer for you where you can visualise your parameter on the sheet - if this isn't selected there is still the option to drag your parameter field over the slicer button alongside the other chart options.
- Viewing/Editing your parameter
Once you've made your parameter, a new table will appear on your right with the name you've given to your parameter. There will be a small drop-down arrow which corresponds to 2 fields - one of which will represent the parameter and the range of values within it (shown with the grid and ? symbol), whilst the other will show the value that has been selected at that time (shown via a calculator symbol). Clicking on either of these will show the relevant DAX code at the top ribbon - by clicking on the parameter field itself, you can edit your min, max and increment values by editing the DAX code.
- Creating a chart and reference line using your parameter
The key thing to note with parameters is that they are completely isolated from your dataset, so will not affect your values unless you give them a means to do so. Here I'm creating a simple clustered bar chart with sales on the X-axis and sub-category in the Y-axis. To help bring context to our new parameter, I'm going to create a reference line showing the target value. Ensuring you're selected on the chart, click on the magnifying glass on the right, and select "Constant Line", then press "+ Add line". Under the "Line" drop-down, you will see a "Value" pane where you can choose where you want your reference line to sit. As we want this to change according to our parameter, we can select the fx on the side, which will open a pop-up menu. Under "What field should we base this on", click "All data" then select your parameter field. Because your parameter is only one value, I would avoid using variance, standard deviation or counts to summarise this field (I usually choose sum or average) to ensure the line shows the correct value selected by the user. Feel free to format the line's appearance so it is easier to see.
- Changing the colour of your bars depending on whether they reach the parameter
Now it's onto the fun stuff. Here we need to make a nested IF calculation which lets the graph know how to react when the sales value is above or below the target. In the Orders table (where your relevant graph information is) select "Create Measure" in the top pane to create your calculation. Here I've named it "Sales Above" and input the following:
This is saying if the Sales are greater than my Parameter value, then input the colour #C0684 (but choose any colour you like - you can also write the name ie "Red", "Dark Blue" if you aren't fussed) - and if your sales are not greater than the parameter, input the other colour (2nd hex code).
Once written, select your bar chart, select the paintbrush/"format visual" tool on the right, go to "Bars" and press the fx button under "Color". In the "Format" dropdown select "Field Value", and in the second dropdown you can select the nested IF statement you've just created from your data.
And voila! All done. Enjoy your dynamic and colourful bars!
Happy colouring!
H