Working with Averages in DAX using AVERAGEX, SUMMARIZE and DATESINPERIOD

In this blog post, we are going to dive into the world of averages and how to solve problems when calculating averages by using CALCULATE, SUMMARIZE and AVERAGEX. Specifically, we look at creating an average calculation across months and as you will see in the video below, this can be a challenge on multiple levels!

Working with SUMMARIZE and SUMMARIZECOLUMNS in DAX

Our goal in this video is to return the average sales for the last 3 months, see Figure-1 below.

SNAGHTML9292404

Figure 1 – Average of last 3 months of sales

Our first attempt at solving this problem may be to just use the expression AVERAGE([Sales Amount]) as seen in Figure-2 below. However, the AVERAGE function will first return the AVERAGE of all the transactions for the month and therefore the results of the following calculation are not quite as expected:

image

Figure 2 – Average of Sales Amount, incorrect calculation.

The result of the expression in Figure 2 can be seen in the following screenshot. Ultimately, average is returning the SUM of the Sales Amount divided by the number of transactions for each month.

SNAGHTML92fc901

Figure 3– Average of Sales Amount

Now that we understand this behavior and what is occurring, we can take a direct approach at solving the problem. First, we need to create a virtual table that contains the months along with their total sales. In order to create this aggregated table we can use SUMMARIZE or SUMMARIZECOLUMNS. In this scenario I will use SUMMARIZE because SUMMARIZECOLUMNS doesn’t quite work correctly within a modified filter context and we will need to change the default filter context to get 3 months in our aggregate table.

The following expression will return an aggregate table with the Year, Month and Total Sales.

image

Figure 4 – Creating a virtual, aggregated table with SUMMARIZE

SUMMARIZE is a function that will return a table expression and therefore you would get an error message if you tried to just put this expression in a calculated measure. However, you could test this out or “DEBUG” this expression by putting it in a calculated table or running the expression from Dax Studio. See the results of this expression from Dax Studio below in Figure 5:

image

Figure 5 – A view of the virtualized table created by SUMMARIZE

Working with AVERAGEX, CALCULATE and DATESINPERIOD

There are a few challenges still left to solve to get our desired result. First, you can’t use the AVERAGE function on a table or table expression, you must provide a column and therefore the result produced by SUMMARIZE here cannot be used by AVERAGE. However, X Functions like AVERAGEX accept a table or table expression as their first parameter and therefore work perfectly with this virtual table.

The next challenge is that the virtual table is returned within the current filter context and therefore we would always be returning the AVERAGE of the current month and only the current month. UNLESS, we use the CALCULATE function to evaluate the expression within a modified filter context, which is exactly what we would do! The calculation in Figure 6 below is the expression that returns the desired result seen in Figure 1.

SNAGHTML94d9dd9

Figure 6 – Final calculation

Dynamically changing title names in Power BI

Hey Everyone! I’m excited that I’m finally getting this blog done! Also, I’ve embedded the YouTube video below Smile

Also, in other news, after 8 years of blogging I finally decided to make some kind of logo, it’s a little unique but I like it! Especially how I was able to sneak the board game in there, because I love board games!

Original on Transparent

Figure 1 – New logo

Here is the video posted on YouTube if you want to watch the steps in action.

What is a Dynamic Title in Power BI?

The idea around a dynamic title is that the name of the title changes automatically to reflect any filters that may be applied. In the automated gif below, watch how the title name changes as I drill down into the chart visual.

DynamicTitles

Figure 2 – Dynamic title, animated gif

Creating Dynamic Titles in Power BI with SELECTEDVALUE() Function

Creating the dynamic title you saw in Figure 2 above requires a little DAX, not much. First, create a new calculated measure and give it a name, in the YouTube video I called this measure Dynamic Title. Then I used the following expression:

image

This is a simple expression which will return the name of the country if only one country exist in the filter context, if not, it will return the text “All Countries”. In this example I am navigating a hierarchy, therefore, I want the title name to change for each level of the hierarchy as new filters are being applied. The following screenshot is the completed code from the video:

image

Conditional Formatting in Power BI on the Title of a visual

The final step is to use the calculated measure on the visualization with the following steps, see animated gif for reference:

  1. Select visual and navigate to the formatting pane
  2. Find the title category and expand it.
  3. Click on the fx icon next to title
  4. Select field value for Format By
  5. Select your measure from the drop down list for Based on Field.
  6. That’s it!

As always, hope this post helped!

2_Conditional Formatting


ALL vs ALLSELECTED in DAX and Power BI Desktop

Hey all! In my latest YouTube video  on DAX I discuss how ALLSELECTED can be leveraged in Data Analysis Expressions (DAX). This continues a series of YouTube videos in which I discuss various DAX functions and how to use them in Power BI Desktop.

Please feel free to check out my YouTube channel and subscribe Winking smile at https://www.YouTube.com/c/MitchellPearson

If you want to watch the video of this function explained, please take a look below:

ALLSELECTED function in DAX and Power BI Desktop

First, let’s look at the definition of ALLSELECTED, provided inside Power BI desktop when referencing the function:

image

ALLSELECTED: Returns all the rows in a table, or all the values in a column, ignoring any filters that may have been applied inside the query, but keeping filters that come from the outside.

What does this mean? Well, it means that it will essentially ignore filters coming from that visual but still respect filters coming from slicers and filters outside that visual (the outer query)! And yes, this is pretty awesome as you will soon see.

ALL vs. ALLSELECTED DAX functions

The biggest difference between ALL and ALLSELECTED is that the ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

Returning Total Sales of all Selected Countries

The following expression in DAX would return the total sales for all countries in all scenarios. For example, the countries Australia, Canada and France were selected in the slicer, but yet the measure Total Sales All Countries still returns the total sales across all countries:

image

Figure 1.1

In this scenario, you might want to return Total Sales for all countries selected in the slicer (allselected), $13,682,863.16. This can be quite tricky, especially if you didn’t know of the existence of the ALLSELECTED function. Let’s write the expression in Figure 1.1 a different way and look at the results.
image

Figure 1.2

In this example you can clearly observe that ALLSELECTED is providing different way to analyze your data. I love this dynamic approach to analyzing data that is provided by simply modifying the ALL function to use ALLSELECTED!

As always, I hope this blog has helped someone somewhere and thanks for reading my blog!!