With this conditional formatting technique, you can show your users a message on the visual (using text or images) instead of a blank visual.Continue reading
Interested in learning how to compare certain budgeted scenarios vs. actuals? In this blog post, I will demonstrate a technique to show you how to do that.Continue reading
The May 2020 Power BI update (which was released today, May 19th, 2020) has a new feature that allows you to set a shadow around your visuals. I had a little bit of fun testing out this feature; I used it to to illuminate KPI cards with conditional formatting to tell a more interesting story.
To illustrate this new feature, I just created a simple Sales vs Sales Target KPI card, as you can see above.
I created a what-if parameter to set the Sales Target value and then calculated % Sales against this Target value. I then created another simple measure to set the shadow colour based on % Sales Target achieved. I used conditional formatting to change the shadow according to the following parameters:
- If the % Sales vs Target is more than 90%, then Green,
- If the % Sales vs Target is more than 70% but less than 90%, then Yellow,
- otherwise Red.
Add following measures (I called it Shadow Color) for conditional formatting and this measure we will use to illuminate KPI card
Sales Status = VAR __targetAchieved = [% Target Achieved] --simple measure to calculate % of target achieved dividing sales by target RETURN SWITCH ( TRUE(), __targetAchieved > .9, 1, --above 90% __targetAchieved > .7, 2, -- above 70% 3 )
Shadow Color = VAR __salesStatus = [Sales Status] RETURN SWITCH ( __salesStatus, 1, "#7FC600", 2, "#F9C700", "#C6001C" )
Let’s add above measure to set the shadow for the KPI Card. Follow steps in next image for conditional formatting:
- Click fx button to set conditional formatting
- Select Field Value in Format by drop down
- Under Based on field choose the measure name, in this case it is called Shadow Color
Finally, make tweak to shadow settings by selecting Custom value in Preset section so that shadow show up around KPI card
And that’s it! You can highlight visuals using this new shadow feature, along with conditional formatting, to create stunning reports. For an example of what the finished product looks like, please see the image at the beginning of this post.
I hope you find this new feature useful and are able to use it to tell a data story within your organization.
More than a year ago, a question was asked on the Power BI community in regards to showing a heat map in Power BI. Not just a simple heat map in the matrix visual, but a heat map based on data smoothing. I cannot find the link to that post (sorry for that!), but I want to share what I learned from it and how I solved it.
The business logic was to smooth data by taking the average of the surrounding two rows and two columns in both directions and to take the average of the value based on the bin size on the columns. In our case, we are assuming that the bin size value for the column is set to 5.
To explain this further, assume we have the following data in Excel and we want to calculate the average of Cell C3; we need to look at the adjacent two rows and two columns.
In this case, the total values will be 243 (SUM A1:E5). For C3, which is in row 3, we are adding the values from row 1 to row 5, and similarly for column C we are adding the values from column A to column E; that’s what translates to A1 to E5 (SUM A1:E5). The average value for this cell (C3) will be 24 – 243 divided by 10. You get 10 by multiplying the bin size (5) by 2, and then 243 divided by 10 rounds to 24.
I hope this gives you an idea on how we are calculating the average of each cell to smooth our data. Let’s see how it all fits into Power BI and how the heat map looks after data smoothing.
Here is some sample data with three columns:
In this example, Length will be on rows with bin size of 150 and Weight will be on columns with bin size of 5.
Let’s first add a bins column to our table in order to add the bin for the Length column.
- Right click on the Length column
- Select New Group
- In the New Groups window, add the bin name – in our case it is called Length (bins) and give it a Bin Size, i.e. 150
Similarly, we are going to add Weight (bin) with the Bin Size value set to 5. Once the bins are added, we will have two new bin columns in our model: Weight (bin) and Length (bin).
Learn more about Grouping and Binning here.
Now it’s time to add a measure to calculate the average for each cell as explained in the beginning of this post, using the Excel sheet as an example.
Smoothing Avg = VAR __weightBinSize = 5 --bin size of our columns VAR __totalWeightBin = __weightBinSize * 2 VAR __myWeightBin = SELECTEDVALUE( FlatData[Weight (bins)] ) --get current weight bin //establish range for weight bin. Go back two columns (bins) and go forward two columns (bins) VAR __minWeightBin = __myWeightBin - __totalWeightBin --go back 2 columns VAR __maxWeightBin = __myWeightBin + __totalWeightBin --go forward 2 columns VAR __lengthBinSize = 150 --bin size of length columns VAR __totalLengthBin = __lengthBinSize * 2 VAR __myconcSpan = SELECTEDVALUE( FlatData[Length (bins)] ) --get current length bin //establish range for length bin, go back two rows and go forward two rows VAR __minLengthBin = __myconcSpan - __totalLengthBin --go back 2 rows VAR __maxLengthBin = __myconcSpan + __totalLengthBin --go forward 2 rows //sum the quantity for the range, it is similar to A1..E5 in our excel example above VAR __totalQty = CALCULATE( SUM ( FlatData[Qty] ), FlatData[Weight (bins)] >= __minWeightBin , FlatData[Weight (bins)] <= __maxWeightBin , FlatData[Length (bins)] >= __minLengthBin, FlatData[Length (bins)] <= __maxLengthBin ) RETURN DIVIDE( __totalQty, __totalWeightBin ) --calculate the average
Alright, now that our measure is in place we can create a matrix with a heat map. To do so, select the Matrix Visual and add Length (bins) on rows, Weight (bins) on columns, and our average measure (Smoothing Avg) in the Value section. This is what it will look like:
Once all of the above is done, this is what the matrix will look like. I love numbers, but this looks super boring:
So, now it’s time to create a heat map, and for this we will use conditional formatting. Before that, however, let’s see what a heat map without data smoothing would look like:
Looks pretty good, but let’s check out how it looks after we use the data smoothing average measure.
Looks better – but let’s take it even further. We really don’t need to see those numbers; in the conditional formatting window, let’s change the foreground and background to the same colour. Here is the output:
I find the above heat map to be much more useful than a heat map without data smoothing.
And this is what we are doing in the conditional formatting window for both the foreground and background colour:
Read more about conditional formatting here.
We can do a lot of amazing things in Power BI to visualize our data and this is just one example. How will you use it? As always, I’m looking forward to your feedback and learning from you all!