You cant see me on The transaction log for databa… Mitchell Pearson on The transaction log for databa… Mitchell Pearson on Advanced TSQL Takeover VINICIUS AUGUSTUS PA… on Azure Data Factory–Filte… Introduction to Wrangling Data Flows in Azure Data Factory.Working with Parameters and Expressions in Azure Data Factory.Dynamically changing title names in Power BI.Managed Virtual Networks and Private Endpoints in Azure Synapse and Azure Data Factory.Working with Averages in DAX using AVERAGEX, SUMMARIZE and DATESINPERIOD.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. 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. However, X Functions like AVERAGEX accept a table or table expression as their first parameter and therefore work perfectly with this virtual table. 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. There are a few challenges still left to solve to get our desired result. See the results of this expression from Dax Studio below in Figure 5:įigure 5 – A view of the virtualized table created by SUMMARIZE Working with AVERAGEX, CALCULATE and DATESINPERIOD However, you could test this out or “DEBUG” this expression by putting it in a calculated table or running the expression from Dax Studio. 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. The following expression will return an aggregate table with the Year, Month and Total Sales.įigure 4 – Creating a virtual, aggregated table with SUMMARIZE
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. In order to create this aggregated table we can use SUMMARIZE or SUMMARIZECOLUMNS. First, we need to create a virtual table that contains the months along with their total sales.
Now that we understand this behavior and what is occurring, we can take a direct approach at solving the problem. Ultimately, average is returning the SUM of the Sales Amount divided by the number of transactions for each month. The result of the expression in Figure 2 can be seen in the following screenshot. 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:įigure 2 – Average of Sales Amount, incorrect calculation. Our first attempt at solving this problem may be to just use the expression AVERAGE() as seen in Figure-2 below. Our goal in this video is to return the average sales for the last 3 months, see Figure-1 below.įigure 1 – Average of last 3 months of sales Working with SUMMARIZE and SUMMARIZECOLUMNS in DAX
0 kommentar(er)
