In our Power BI training courses, I show people how to add a Constant Line to a clustered column chart and I then, rather glibly, dismiss the other lines by explaining that "they are calculations across the totals". But what actually do I mean by this; what are these calculations? I do feel that these Analytics Lines deserve a much better explanation so now is the time to turn the spotlight on them.
Firstly I'm going to take a closer look at the Constant Line to show you how to display the data label and value (what should be a simple thing actually takes a number of steps). Then, I'm going to unravel the calculations behind the "Average", "Max" and "Min" lines.
Let's take the Constant Line first.
The "Constant Line", as its name suggests is pretty much that; a reference line that doesn't change with your data. Let's use the Constant Line to put a Target line of £400,000 for our Salespeople on a column chart visual as shown here:-
But clearly this line is of no use unless you know that it's the Target! So how do we label it? Well the starting point is to give the line a meaningful name other than "Constant Line 1". To do this, you can simply double click on the name and retype e.g. "Target". You can then turn the data label on and change the "Text" option to name.
Useful though this line can be it's a little limited in what it can do for you; most people want a dynamic line that changes as the data changes.
So let's turn our attention to other Analytics lines that do just that. I've added a "Max", "Min" and "Average" line and turned on the data labels to show the value of the lines. As mentioned above, these data labels show calculations for the totals for each Salesperson (as opposed to row level calculations):
These data labels are easy to display but what if you want to calculate these values yourself? Admittedly, if you want to show the Max and Min of totals in a visual, you could just to sort on a the visual like this:-
But what about the Average? How would you create a measure to calculate this value? And indeed, why would you want to? I can think of at least three reasons.
Firstly, you may want to display this value in a Card:-
Secondly, the default data label is too small to see and unfortunately, you can't increase the font size of Analytical Line data labels. An improvement might be again to display this average in a Card and then place on the Column chart:-
Thirdly, you might want to calculate how much each Salesperson's totals differed from this average so you can then plot this in a Column chart:-
So how do you create the measure for this Average (and indeed how would you calculate the Max or Min of the Totals, rather than just sorting)?
Just like most DAX expressions, this measure for the average is pretty simple to type in …….………….but more challenging to understand.
The expression removes any filters from the Salespeople table using ALL so we now have a table containing all the salespeople. The Total Sales are then calculated for each Salesperson. Please note that "[_Total Sales]" must be a measure because the expression uses context transition (For more information visit https://www.sqlbi.com/articles/understanding-context-transition/). Because we've used ALL, the AVERAGEX function can then find the average of all the Salespeople's totals.
Please note that if you want to find the Max or Min across the totals, just use MAXX or MINX instead of AVERAGEX.
However, let's now have a go at finding the average for years.
And now you're going to find that when you try to calculate the Average for Totals across Years, this calculation doesn't work:-
We can see that we get the wrong average of £41,794, when the correct calculation should be £12,548,756. To see why this measure doesn't calculate correctly, we can apply the same explanation to this measure as to the one above, The measure removes any filters coming through from the Date Table, in this case it removes the filter from the Year, so now we have a table that contains all the dates from the DateTable. The Total Sales are then calculated for every date in the date table for which there are corresponding sales in the Sales table and finds the average of these (1,201 dates have corresponding sales so it's 50,195,025 / 1,201 = 41,794 ).
So this will always be a problem when you're working with different granularity in the same table. What we need is a different measure that will calculated the averages at yearly granularity (rather than date granularity) as in this example:-
So this is the correct expression for the Yearly Average measure:-
This measure first creates two variables. The first one, "alldates" uses the ALL function to create a table that has removed all the filters from the Date Table (the Year filter in this case) and so returns the DateTable containing all its rows. The second variable, "mytable" creates a table that groups the "alldates" table by Years and then calculates the "Total Sales" measure for each year. The AVERAGEX function then finds the average of these Sales.
So now let's take this a step further and look at calculating the Monthly average:-
The Monthly Average measure works in much the same way as the "Yearly Average" above except that the SUMMARIZE function now creates a date table that is summarised by both year and month. The FILTER function is used inside AVERAGEX to filter out months that have no sales e.g. if the current month is July and the current year is 2019, there only 7 months for 2019 as opposed to 12 for the others.
And finally let's do it; the Monthly Average for each year:-
The "allmonths" variable creates a table the contains both ALL the month numbers and ALL month names for the year in the current filter context (e.g. 2018). (The reason you need both month number and month name is because there is a custom sort order on Month name and so you need to pass the ALL function over both the sorted column and the column it's sorted by). The "mytable" variable groups the "allmonths" table by month number and finds the Total Sales for each month. Again, the FILTER function is used inside AVERAGEX to filter out months that have no sales e.g. if the current month is July and the current year is 2019, there only 7 months for 2019 as opposed to 12 for the others.
So if you like displaying these Analytical lines, you may now want to do more with these calculations.
If you want to learn more DAX and create measures like the ones above, we run regular DAX courses in London, or we can run an on-site course customised for your company. See our DAX web page for public course dates, or contact us for an on-site course quote.