In this writing, I want to share how I author a calculated measure using the WINDOW DAX function when describing "How much percentage of customers are creating the top 80% of sales?".
So far, I generally used the WINDOW DAX function when redefining a date filter. For instance, in one of the cases, I used it when I was asked to show a three-month rolling sum. In other cases, I used it when end users requested to illustrate year-to-date sales. Almost all cases were related to selecting or defining a certain date frame. And then, I compared it with using time intelligence DAX functions like DATESBETWEEN, DATESYTD, DATESINPERIOD, and so forth, in order to understand which one is more performant.
Not only redefining a date range but also the WINDOW DAX function can be utilized when end users ask about how many customers are creating the top 80% of sales. in other words, the WINDOW DAX function is also utilized other than only requesting to change a certain date frame.
I used the AdventureWorksDW2019 sample, and I only selected 4 tables like below for a demonstration. The sample pbix file is attached at the end of this blog post.
The sample has a total of 699 resellers. In the year 2013, 278 resellers were active in the United States, and the sales amount was 19,321,567.
Total resellers count: = COUNTROWS ( DISTINCT ( DimReseller[ResellerName] ) )
Active resellers count: = CALCULATE ( [Total resellers count:], SUMMARIZE ( FactResellerSales, DimReseller[ResellerName] ) )
Reseller sales: = SUMX ( FactResellerSales, FactResellerSales[OrderQuantity] * FactResellerSales[UnitPrice] )
And then, I added the [Ranking] measure and [Top rank ratio] measure.
Ranking: =
VAR _condition =
DIVIDE ( [Reseller sales:], [Reseller sales:] )
RETURN
RANKX ( ALLSELECTED ( DimReseller[ResellerName] ), [Reseller sales:],, DESC ) * _condition
Top rank ratio: = VAR _activeresellers = CALCULATE ( [Total resellers count:], SUMMARIZE ( ALLSELECTED ( FactResellerSales ), DimReseller[ResellerName] ) ) VAR _rank = [Ranking:] VAR _condition = DIVIDE ( [Reseller sales:], [Reseller sales:] ) RETURN IF ( HASONEVALUE ( DimReseller[ResellerName] ), DIVIDE ( _rank, _activeresellers ) * _condition ) As a final step, I added [Cumulative sales ratio] measure like below. As shown in the below figure, 80% of sales in the United States in the year 2013 were created by 31% of all the active resellers.
Cumulative sales ratio: =
VAR _rank =
RANKX ( ALLSELECTED ( DimReseller[ResellerName] ), [Reseller sales:],, DESC )
VAR _cumulative =
CALCULATE (
[Reseller sales:],
WINDOW (
1,
ABS,
_rank,
ABS,
ADDCOLUMNS (
ALLSELECTED ( DimReseller[ResellerName] ),
"@sales", [Reseller sales:]
),
ORDERBY ( [@sales], DESC )
)
)
VAR _allsales =
CALCULATE ( [Reseller sales:], ALLSELECTED ( DimReseller[ResellerName] ) )
VAR _condition =
DIVIDE ( [Reseller sales:], [Reseller sales:] )
RETURN
IF (
HASONEVALUE ( DimReseller[ResellerName] ),
DIVIDE ( _cumulative, _allsales ) * _condition
)
When creating the above measure, my thought process was,
Inside the WINDOW DAX function, I create a RELATION table by adding the sales amount column, "@sales", to the [ResellerName] column.
The relation table is ordered by [@sales] column.
When defining a window, the starting point is 1+ABS. And the ending point is 0+REL.
However, 0+REL produced an error. So, I decided to input the rank number+ABS. Because rank number+ABS also represents the same information as 0+REL.
I did not use PARTITIONBY DAX function in the above, because most of the filters are existing outside of the table visualization.
However, if the territory filter is not created on the report but the inquiry is to separate resellers by territory, the below might be one of the use cases of the PARTITIONBY DAX function to demonstrate only top resellers in each territory.
Cumulative sales ratio partionby territory: = VAR _rank = RANKX ( ALLSELECTED ( DimReseller[ResellerName] ), [Reseller sales:],, DESC ) VAR _cumulative = CALCULATE ( [Reseller sales:], WINDOW ( 1, ABS, _rank, ABS, ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( FactResellerSales ), DimReseller[ResellerName], DimSalesTerritory[SalesTerritoryCountry] ), "@sales", [Reseller sales:] ), ORDERBY ( [@sales], DESC ), KEEP, PARTITIONBY ( DimSalesTerritory[SalesTerritoryCountry] ) ) ) VAR _allsales = CALCULATE ( [Reseller sales:], ALLSELECTED ( DimReseller[ResellerName] ) ) VAR _condition = DIVIDE ( [Reseller sales:], [Reseller sales:] ) RETURN IF ( HASONEVALUE ( DimReseller[ResellerName] ), DIVIDE ( _cumulative, _allsales ) * _condition )
Reseller sales top80% partitionby territory: = VAR _resellerlist = FILTER ( ALLSELECTED ( DimReseller[ResellerName] ), [Cumulative sales ratio partionby territory:] <= 0.8 ) RETURN CALCULATE ( [Reseller sales:], KEEPFILTERS ( DimReseller[ResellerName] IN _resellerlist ) )
To summarize, the WINDOW DAX function can be utilized not only when re-defining the date range but also when ordering the category by sales amount and then defining the top category range or the bottom category range.
I hope this helps to have more fun learning the WINDOW DAX function.
Comments