Visual Hack: Dynamic Scale with Horizontal Stacked Bar Chart

In the above video you see what looks like a custom visual in Power BI.  However, I created this with a few simple steps by utilizing the built-in horizontal stacked bar chart in Power BI and a color scale created in Excel that is used as the background in the plot area.  Here’s how I did it.

Note:  This sample scenario uses project risk status but it can be implemented in other contexts.  It’s up to you to take what you learn here and apply to your specific data.

Step 1: Create a derived column that converts the risk status text to a numerical value.

In the Query Editor, click on Add Column and Conditional Column.

conditional_column

Input your conditional logic.  For this example, it’s as follows:

conditional_column_input

Step 2: Create required measures.

First, create a measure that averages the derived column (Risk Num).

avg_risknum

Then, create a Risk Line measure.  This will serve as the line/data point/needle in the visual and will go up and down the scale as you slice your data.

risk_line

Note: I used a value of 0.015 for this example, as the range of the scale is small – between 1 and 3.  You may have to use a different value depending on the range of your scale.  You may have to play around with it a few times until it looks right in the visual.  If I used a value of 0.015 on a large scale, then you wouldn’t be able to see the line.

Now, create two additional measures, one that represents the section under the needle and one that represents the section above the needle.  I’m calling these Under Risk and Over Risk but you can call them what you want.

under_risk

Explanation of Under Risk: Since this represents the section under the needle, I could simply use the Avg Risk measure, but then the needle would be slightly above the actual value for Avg Risk when I want the needle to be centered on the value of Avg Risk.  In order to achieve this “centering”, I need to subtract Risk Line (value = 0.015 in my example) divided by 2 from the Avg Risk value.

over_risk

Explanation of Over Risk: Since this represents the section over the needle, I need to take the max value in the scale (3 in this example) and subtract from it the Avg Risk plus the Risk Line divided by 2.  Again, dividing the Risk Line value by 2 achieves this centering.

In short, you need to account for the Risk Line value in the stacked bar chart.  That’s why I divide the value by two in my measures, allowing the needle to be centered on the Avg Risk value.

Step 3: Input the measures and format

Input the Under Risk, Risk Line, and Over Risk measures in that order. 

initial_img

Now, remove the legend and the gridlines and change the color format of the Under Risk and Over Risk measures.

stage_2_img

Step 3: Create and add the color scale.

This color scale is achieved with the help of our good friend, Excel.  Open up a blank worksheet and highlight a row of cells based on the color formatting you want to use, like so:

scale_in_excel

You need to use a screenshot tool, like Jing, to create a screenshot just of the colored cells.  Save your screenshot.

Now, we just need to add this screenshot to the plot area in our visual.  Make sure you select Fit for the Image Fit and then change the transparency to your liking.

final_img

You’re done!  Now start slicing away and watch the needle go up and down the scale.  You essentially created a custom visual without any Javascript or HTML.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s