In some special scenarios, we often need to calculate some specific groups, such as stores, etc., 3 days before the opening of the sales situation, but the opening date of each store, and often different, direct calculation is often tricky.
Today we are going to share two quick solutions for calculating such problems using calculated columns and DAX metrics.
I: XMIND
II: Example data
2.1 Description of example data columns
For ease of validation and clearer examination of the results, the data source uses only three columns for three stores, A, B, and C, a date column, and a simulated turnover data column.
2.2 Calculation objectives
The goal is to calculate, using the DAX or Calculation column, the sum of the turnover of the first three days in which each store actually generated sales
III: Calculated program
First, we solve it with a computational column scheme.
The quickest or most direct way to encounter this type of problem is to label each row of our data with data from the calculated columns, and then use the CALCULATE calculator's filters to filter the labels we want, and then get results
Below the calculation of the column scheme, the first step is to find the minimum date through the MIN function, because we are calculating the minimum date of each store alone to get the sum of sales, so here the use of ALLEXCEPT, to remove the store column filtering, and get the total of each store to the opening of the first three days of the data labels.
Finally, through the CALCULATE function, the data that meets our requirements, summing, we will arrive at our results.
The specific DAX codes are listed below:
The first step is to add a new calculated column for the statement of operations IsFirst3Days = VAR MinDate = CALCULATE(MIN('Example table'[dates]), ALLEXCEPT('Example table', 'Example table'[(retail) store])) RETURN IF( 'Example table'[dates] >= MinDate && 'Example table'[dates] <= MinDate + 2, "Store dates three days prior to opening.", "Other" ) In the second step, the metric calculates the turnover, using the labels from the first step to filter and calculate the IsFirst3Days_sum or volume of business= CALCULATE(SUM('Example table'[sum or volume of business]), 'Example table'[IsFirst3Days]="Store dates three days prior to opening.")
IV: DAX Program
So is there a more convenient, or one-step solution to the problem?
Of course there is, let's look at pure DAX solution ideas.
First of all, the idea is the same as calculating the column scheme, we also use the form of labels to filter the table, and then directly sum the eligible rows to get the target value
The following DAX, the first use of the FILTER function, the example table for screening, with ALLEXCEPT function, remove the table screening conditions, and finally directly with the CALCULATE function for aggregation.
One difference is that when calculating the column scheme, we used IF to make a judgment, and in the DAX scheme, we used the juxtaposition condition, i.e., the rows that satisfy both conditions we keep, and a little bit of trivia, in DAX we'll be using "&&" to express our notion of "and".
Finally, we can see that the results are the same for both scenarios.
Pure DAX program: TotalSalesFirst3Days = CALCULATE( SUM('example table'[turnover]). FILTER( 'example table', 'example table'[dates] >= CALCULATE(MIN('example table'[dates]), ALLEXCEPT('example table', 'example table'[(retail) store])) && 'example table'[dates] <= CALCULATE(MIN('example table'[dates]), ALLEXCEPT('example table', 'example table'[(retail) store])) + 2 ) )
See here, some friends may ask, why will introduce two programs here, and not just DAX, obviously DAX one step can solve the problem?
Because, even though computing groups, which wastes memory, has considerable performance limitations in large dataset tables, in some emergencies (it is too late to conceptualize a DAX) or some special scenarios where we need to solve the problem first and foremost, we are encouraging more problem solving ideas.
So, when performance is sufficient, there is no need to obsess about the solution, just solve the problem, and it's not too late for us to put performance optimization on the back burner.
V: Key DAX-ALLEXCEPT Functions
5.1 Grammar
ALLEXCEPT(<table>, <column1>, <column2>, ...)
5.2 Role
ALLEXCEPTThe function returns a table that removes all filters except those applied to the specified columns. This is useful when aggregation calculations need to be performed on certain columns, independent of the filters applied to other columns.
5.3 Scenarios of use
Calculate the percentage of: For example, if you want to calculate a city's share of sales in a province, you can use theALLEXCEPTThe function keeps the province filter and removes the other filters.
Complex aggregation calculations: In performing complex aggregation calculations, theALLEXCEPTHelps you control which column filters are retained and which are removed for more flexible data analysis.
5.4 Other similar functions
ALL: removes all filters and returns the entire table or column
ALL('Sales data')
ALLSELECTED: removes all filters, but retains the filters selected by the user in the visual object
ALLSELECTED('Sales data')
REMOVEFILTERS: Removes the filter for the specified column or table.
REMOVEFILTERS('Sales data'[provinces])
KEEPFILTERS: Keep the existing filters and add new ones to them.
CALCULATE([sales volume], KEEPFILTERS('Sales data'[municipalities] = "Chengdu"))
VI: Summary
In practice, this type of business, based on specific subgroups, such as groups, product categories, specific stores, etc., to carry out statistical scenarios is very common, and the flexible use of the ALLEXCEPT function, which we talked about today, can precisely help us to solve this type of problem!
This type of problem, generally contains a series of scenarios such as date, grouping percentage, grouping ranking, etc., it is worth our time to study, and I will make additional case illustrations in the follow-up if there are actual cases occurring.
ENJOY DAX