In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the SUM() function to add up the values on the sheet.
However; in our model, we had a total line in our Vehicle Type column; as a result, we needed to use a different CUBEVALUE() formula for total lines as opposed to the individual data lines.
We will be using the Canadian Car Sales model that uses data available from Statistics Canada and is available on this site.
What we have done so far…
In prior exercises, we created a table for calculating car unit sales by month, quarter, region and vehicle type.
This table used:
- A custom measure in the data model to get the sum of all cars.
- The CUBEVALUE() function to take the values out of the data model.
- A custom field to determine the quarter that the cars are sold in.
If you were to look at the totals, it uses the word “Trucks” (in cell D6) to determine the total for all trucks. However; it uses the words “Total, new motor vehicles” for the total.
If the data was modified and totals were removed (as they are redundant), it would cause the formula to break.
In this post, we will walk through excluding the totals using a combination of FILTER and CALCULATE, without changing the underlying data.
Why can’t we just modify the CALCULATE?
Modifying the CALCULATE function without using a FILTER will not provide the correct results.
To show why this is not possible, we will modify our CALCULATE function directly to exclude the totals.
Under the Power Pivot, Measures menu, select Manage Measures.
Select the CarSalesTotalValues measure, and click Edit.
Adding the condition directly to the CALCULATE function.
Instead of relying on a FILTER function, it would make sense to add a new condition to exclude “Total, new motor vehicles” in the CALCULATE function.
Since the CALCULATE is basically a filter, this should work, right?
In this example, we add CarSales[Vehicle type] <> “Total, new motor vehicles” to the CALCULATE function.
This changes the CALCULATE function to:
=CALCULATE(sum(CarSales[VALUE]), CarSales[Origin of manufacture]="Total, country of manufacture",CarSales[UOM]="Units",CarSales[Vehicle type]<>"Total, new motor vehicles")
Let’s see what happens.
This change has caused the measure to return all values as the total. The CALCULATE formula is telling the CUBEVALUE to ignore the arguments from row 6.
Instead, it overrides the arguments in CUBEVALUE and tells it to return any value that is not “Total, new vehicles”. As a result, all the values for each month are the same.
How else can we modify the CALCULATE function?
Instead of modifying the CALCULATE function directly, we need a way to modify the data. This modification should exclude the “Total, new vehicles” argument without impacting the CUBEVALUE formula.
FILTER() to the rescue!
The FILTER function is used to eliminate rows from a table that are not needed as part of our calculation.
This function, according to Microsoft’s documentation:
Returns a table that represents a subset of another table or expression.
https://docs.microsoft.com/en-us/dax/filter-function-dax
What this will allow us to do is to subset the CarSales table, eliminating the “Total, new vehicles” rows.
Using a FILTER
The filter function is made up of 2 parts:
- The table to be filtered.
- Conditions that are being added in order to filter the table.
Our FILTER formula will look as follows:
FILTER(CarSales,CarSales[Vehicle type]<>"Total, new motor vehicles")This formula adds a new condition to exclude all values of “Total, new motor vehicles” in the Vehicle type field, within the CarSales table.
After adding the FILTER, our CALCULATE function will look like this:
Note – within the CALCULATE, after the first argument, all other arguments can go in any order.
This broke my totals!
After adding the FILTER to the CarSalesTotalValues measure, the total column is now showing blank!
The reason for this is that the Vehicle type is still looking for a value of “Total, new motor vehicles”. Now that we have added a filter to remove this value, the CUBEVALUE cannot find any value for the total.
It is easy to fix this! By removing the “Total, new motor vehicles” parameter, the query will only include the non-total lines.
After removing the total parameter, the value shows up as expected! Copy the formula down to all the other dates in the total column.
For the grand total, there should be only one parameter; the geographic region!
Next Steps
After removing the total from the CUBEVALUE, the final step is to change the percentage of row formula. In order to make that change, a new measure must be created.
This new measure will use the ALL function to remove a filter in order to get the balance of the entire line within a CALCULATE function.