Welcome back to this week’s edition of the Power BI blog series. This week, we look at how to create a Threshold Column chart to highlight columns that rise above a set threshold.
The goal of this kind of visual is to plot values on a normal Column chart but highlight the ones that rise above a certain threshold, in this case, the average. To make things difficult, we want to be able to adjust this for the averages of different years. There is no built-in option to achieve this in Power BI currently, which means we need to get a little bit creative.
Chart Data
We are looking at the [Sales] column in the financials table and will be constructing two measures as follows:
Total Sales =
SUM(financials[Sales])
Average Sales
= IF([Total Sales], CALCULATE(AVERAGEX(dcalendar,
[TotalSales]),ALL(dcalendar),VALUES(dcalendar[Year])))
The Average Sales measure calculates the
average sales of a dataset over a calendar year using the AVERAGEX function, which calculates the average on a record by record basis. Meanwhile, the Total Sales measure is
just the total sales for a given period.
Next, we are going to create two more measures using
those we just made.
Below Average
= IF([Total Sales] >= [Average Sales] ,[Average Sales] ,[Total Sales])
Above Average
= IF([Total Sales] >= [Average
Sales],
[Total Sales] – [Average Sales] ,BLANK())
The Below Average measure here is comparing
average sales and total sales and is taking the lower of the two. The Above Average measure returns the
value of Total Sales above Average Sales, returning zero [0] if it
is less. These measures split the Total
Sales into two [2] segments based upon the Average Sales (or any
other threshold you might want to use).
Building the Chart
Now that the measures are set up, the next step is to
build the chart. We will proceed to the
Visualizations tab and create a Line and Stacked Column chart:
The x-axis will be Year and Month,
while the column y-axis will be Below Average followed by Above
Average. The line y-axis is
just Average Sales and will function as our threshold.
This should leave us with a threshold chart
highlighting sales above the average for the given year. From here, we can change the colours and
styles in ‘Format Visual’ to our liking to make it more appealing.
That’s it for this week’s Power BI blog. Next week, we will go through some interesting
ways we can customise this visual. If
you wish to catch up on past articles, you can find all of our past Power BI
blogs here.
Be the first to comment