In the last post, we created a custom field in the Excel data model; however, we still needed to use Excel formulas to calculate the percentage of a row.
In this example, we will be using a simple CALCULATE() function in the data model to create a percentage of total sales metric to our CUBEVALUE() function in Excel.
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, a table for calculating car unit sales by month, quarter, region and vehicle type was created.
This table used the CUBEVALUE() function to get values from the data model.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,”[CarSales].[UOM].[Units]”,”[CarSales].[Origin of manufacture].[Total, country of manufacture]”,”[CarSales].[GEO].[“&$B$4&”]”,”[CarSales].[Quarter].[“&$A11&”]”,”[CarSales].[Vehicle type].[“&B$6&”]”)
We also created a custom field for quarterly values in the data model. This meant that we would not have to use SUM() or SUBTOTAL() functions in Excel.
What we still need to add…
There are still 2 more places on the table that use Excel formulas instead of taking data directly from the data model using CUBEVALUE().
Percentage of Total – The Excel sheet is still calculating the percentage of rows using an Excel formula. This percentage formula refers to columns and rows, limiting the formula to being used on the spreadsheet.
Total for Columns – The Excel sheet is still adding together the columns on the sheet using a SUM() formula. A more advanced function is required in order to calculate this number properly since there are totals in underlying fields.
The CALCULATE() function to the rescue!
The CALCULATE() function can only be used in the data model; this is not a function that can be used in normal Excel functions.
This function, according to the Microsoft documentation:
Evaluates an expression in a context that is modified by the specified filters.
https://docs.microsoft.com/en-us/dax/calculate-function-dax
What this means is that for specific calculations within a pivot table, we can override default functionality in order to filter out values that we do not want.
Filtering out values at the time that the data is imported is also possible. Using CALCULATE() is only one way to remove unwanted values!
Editing an existing measure
A calculate function takes a measure, then modifies the measure using filters. In this example, the SUM() created earlier is used as the base measure.
Existing measures can be found under the Power Pivot menu. Select the Manage Measures option within the Measures menu.
Once you have entered the Manage Measures screen, select the CarSalesTotalValues measure and select Edit.
This brings us to the measure editing screen.
Setting up the CALCULATE()
The CALCULATE() function is made up of 2 parts:
- The measure that is being calculated.
- Filters that are being applied to modify the standard calculation of the measure.
The Measure
In this case, the measure that is being calculated does not change. The measure remains sum(CarSales[Value]). At this point, our CALCULATE() formula should look like the following:
Note – there are no errors in this formula. Using the CALCULATE() formula without any filters will simply return the calculated measure, as if there was no CALCULATE() formula used.
The Filters, Part 1
In the original CUBEVALUE() formula, there are 2 arguments that need to be applied to every formula. These arguments were:
- The UOM is always Units.
- The Origin of manufacture is always “Total, country of manufacture”.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,“[CarSales].[UOM].[Units]”,”[CarSales].[Origin of manufacture].[Total, country of manufacture]”
To make our CUBEVALUE() function more readable, move these arguments into the CALCULATE() function.
In order to make a basic filter, select the field from the data model. Next, add the text that you are trying to filter out.
CALCULATE() can be used for very advanced calculations as well – this basic calculation strongly resembles a SUMIFS(). More advanced calculations do not!
Once you have completed adding the new filters, your formula should look like the one below.
This should not change any of the values on your spreadsheet. However; the CUBEVALUE() function can now be simplified! By removing the [UOM] and [Origin of manufacture] arguments, it leaves a much simpler formula!
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,”[CarSales].[GEO].[“&$B$4&”]”,”[CarSales].[REF_DATE].[“&$A8&”]”,”[CarSales].[Vehicle type].[“&B$6&”]”)
The Filters, Part 2 – Vehicle Type
If you try to copy your new CUBEVALUE() formula over into the Total column, you will get a #N/A error.
The reason for this error is that the GETCUBEVALUE() is trying to find a vehicle type named “Total”, which does not exist. Since the formula is trying to get a total value, it makes sense to remove the filter entirely.
Formula that returns #N/A.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,”[CarSales].[GEO].[“&$B$4&”]”,”[CarSales].[REF_DATE].[“&$A8&”]”,“[CarSales].[Vehicle type].[“&F$6&”]”)
Filter removed formula.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,”[CarSales].[GEO].[“&$B$4&”]”,”[CarSales].[REF_DATE].[“&$A8&”]”)
However; this doesn’t seem to work properly either. The total value of 240,358 units is double what we are expecting to find as the total of units.
The reason for this value doubling is that there is a “Total, new motor vehicles” value in our vehicle type field. Since we were using a SUM() to add up the values of all vehicle types, this field was never a concern earlier.
Instead of modifying the CALCULATE(), we will modify the CUBEVALUE() function to add the “Total, new motor vehicles” criteria.
Using the CALCULATE() function to exclude a value in the Vehicle Type column (such as CarSales[Vehicle type] <> “Total, new motor vehicles”) would create a formula that would create a rule for all columns.
This would mean that trucks and passenger car columns would show totals as well.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[CarSalesTotalValues]”,”[CarSales].[GEO].[“&$B$4&”]”,”[CarSales].[REF_DATE].[“&$A8&”]”,“[CarSales].[Vehicle type].[Total, new motor vehicles]”)
Making this modification will show the correct total in the Total column.
Next Steps
The next step is to modify the percentage of the total column. In order to accomplish this, the CALCULATE() function will be used again. However; instead of adding filters, we will be using CALCULATE() to remove filters.