Power BI is great for visualizing and interacting with your data. In this article, let me share a technique for creating variance chart in Power BI. Variance charts are perfect for visualizing performance by comparing Plan vs. Actual or Budget vs. Actual data.
This is what we are going to build.
What you need to build a variance visual in Power BI?
You need a few basic measures.
- Actual values
- Variance
- Variance %
These measure definition will depend on what you are visualizing. So I won’t go in to that detail, but 99% of time, they will simple aggregations such as SUM or AVERAGE.
Power BI Variance charts – Video
If you want a quick walk thru of the variance chart construction, check out below video. Read on for text instructions.
Variance chart in Power BI – Step by step instructions
Step 1: Make a bar / column chart with Actual values
This is easy. Just make a bar (or column) visual with actual values. It will look like this.
Step 2: Add a similar chart for variance and place it next to the actual chart
I would just copy and paste the visual and change the measure. We will end up with this.
Step 3: Apply conditional formatting rules to color variance bars
Select variance visual, go to Format > Data colors and click on fx button to apply conditional formatting rules. You want green color for positive values and red for negative values (assuming positive variance is good, else flip your rules).
The rule setup looks like this:
At this stage, our variance chart looks like this. It has a teeny tiny problem. The sort order is not in sync between both visuals. ?
Step 4: Synchronize sort order trick
You can sort both visuals in alphabetical order, but that would make the charts less useful. So let’s stick with the descending order of actual values.
Just add “Actual Sales” measure to the tooltip area of variance bar chart. Now you can sort that chart on “Actual Sales” too. Pretty neat eh?
Step 5: Tidy up the formatting & group visuals
This is the last step. Just clean up the formatting of both charts. These are the steps:
- For actual values bar chart:
- Color the bars in something dull
- Remove Y & X axis titles
- Remove visual title
- Variance bar chart:
- Adjust Y axis “inner padding” . This makes the bars thin. See right.
- Remove Y axis – title & all
- Knock off X axis titles and visual title
- Make sure both visuals are in same height and top-aligned. Adjust the width of variance bar chart if not done.
Now just select both visuals (hold CTRL key to multi-select) and group them. This way you can move or resize them together.
That is all. Your variance chart is now ready. Enjoy it.
You can enhance it by adding a table that shows detail, something like this:
Download Variance Chart sample workbook
If you are not sure about the instructions or just want a ready to use example, here is the Power BI workbook. This file also features a tool-tip that shows performance at sub category level.
New to Power BI? Check out this excellent getting started guide.
Want something like this in Excel? See this budget vs. actual chart.
Note: Thanks to recent webinar Andrej & Reza for the inspiration to this article.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub