In this post, along prior two posts, I discussed a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles in Canada. This post will import data into the data model and use the cube function CUBEVALUE() along with a basic KPI to extract monthly values.
The purpose of this post is not to discuss the trend of sales of cars in Canada, but instead to provide methods for creating a quarterly analysis using Excel-based methods.
This is the 3rd post using this quarterly report:
- The first post uses the SUMIFS() formula and is available at https://macrordinary.ca/2019/10/29/the-quarterly-report-using-sumifs/.
- The second post uses a pivot table and is available at https://macrordinary.ca/2019/11/07/quarterly-report-pivot-table/.
The Data Set
The data set that we will be using is the 2018 new motor vehicle sales, which is publicly available at Statistics Canada. https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=2010000101
This information is available through a .CSV file available on this site:
Car Sales
The top of the data set should look as follows, with the first 2 columns being REF_DATE and GEO.
Relevant columns in the data set for this analysis include:
- REF_DATE – The month where data is being measured.
- GEO – The geographic region (province or country).
- Origin of manufacture – This shows where the vehicle was made.
- Vehicle type – The type of vehicle being measured (truck or passenger car).
- UOM – Unit of measurement; units or dollar sales.
- Value – The total amount of sales. (in units or thousands of dollars, depending on the unit of measurement)
The Report
The objective of the analysis is to generate the same report as the image below. It must have the following features:
- The report will show the number of units and the percentage of total units sold by vehicle type in a given period.
- Results must be able to be viewed on a monthly, quarterly and annual basis.
- Results must be broken down by both vehicle types, Passenger Cars and Trucks.
- A filter must be available to show results by region.
- Quarterly and total results must be in bold.
- Quarterly and total results must have top and bottom borders.
Now that we know what we are trying to build, it’s time to start building the report!
Step 1 – Import data into the Data Model
The best way to get the data from the .CSV file is to import using the “From Text/CSV” option under the Data tab.
After you have navigated to the correct directory, the CarSales CSV file should be available for import.
Once the results are showing, change the load option to “Load To…”
Since we are importing the data into a cube, there is no need to show the underlying data anywhere in our spreadsheet. As a result, we only need to create the connection, and import the data to the data model.
Once this is complete, the table will appear as one of the Queries & Connections on the right side of your spreadsheet. If it shows that the rows have been loaded into the spreadsheet, then the data has been imported in the data model!
Step 2 – Create a new measure in the data model.
In order to properly aggregate a total using the CUBEVALUE() function, you need to provide a formula that the data model can use for making a sum.
Within the Power Pivot menu, select Measures, then select New Measure. This brings up the Measure window where the sum formula can be created.
Step 3 – Set up the new variable in the data model.
First, you will need to rename the measure name from “measure 1” to something that is easier to understand. Since this is calculating the total of the Values field from the CarSales table, I am picking CarSalesTotalValues as the measure name, but am adding a description to be sure to remember it properly.
Next, add in the sum function for the VALUE column. The available columns for the sum formula will appear as you type, so there is no need to remember the exact name and spelling of the column in most cases.
Once the formula is complete, check to make sure that you didn’t make an error using the Check formula button. This isn’t a required step, but can help if you’re using a more complex formula.
Step 4 – Adjust formatting on the CarSalesTotalValues measure.
Finally, the new measure should be provided default formatting. While you could change the formatting within Excel, this makes it easier to export values using the CUBEVALUE() function.
First, change the category from General to Number. This will enable the number specific formatting options.
Next, change the format to Whole Number. Since this particular analysis involves counting car unit sales, there shouldn’t be any fractional numbers. Using the 1000 separator is not required, but can be recommended to improve readability of your formula.
Finally, click OK and you’ve created your measure!
Step 5 – Add the formatting to the spreadsheet.
Since the formulas will use the names from the cells on the sheet, the blank template should be set up as follows.
Note that the dates are in text format “2018-01”, and are not dates that have been formatted to appear as year-month.
Step 6 – Add the CUBEVALUE() function for total units.
The CUBEVALUE() function is made up of 3 elements when getting a measure in Excel:
Data Model – The data model will always be “ThisWorkbookDataModel”
Measure – The measure that is being calculated, in this case the CarSalesTotalValues measure.
Conditions – Filters that are being applied to show the measure under specific conditions, like months.
When you type the CUBEVALUE() function and enter a left quotation mark, the only option to pick is ThisWorkbookDataModel. Once that is in quotations, add a comma to add the next argument.
For the next argument, there are 2 options, CarSales (the table) or Measures (the measures).
Once measures has been selected, add a period. This will bring up the list of measures that are available. CarSalesTotalValues is the only measure available, so it should be selected.
Your formula should look like this:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[CarSalesTotalValues]")The formula now shows 716,341,613; this is the total for the entire VALUE column in the CarSales table.
Step 7 – Apply filter values to the CUBEVALUE() function.
Regardless of the month, region or car type, there are 2 filters that need to be applied to all the cells.
- The UOM field needs to be set to Units.
- The Origin of manufacture field needs to be set to Total, country of manufacture.
Adding an additional filter is the same as adding the measure, but instead of selecting Measures, select CarSales. This will bring up a list of fields in the table.
However; when we select [CarSales].[UOM], the only option that is available is [All]. Using [All] will provide a total for all values, effectively applying no filter. However; if you type in [Units] instead, it will filter the column for rows with the value “Units”.
Once we add Units as the unit of measurement, the cell shows the total units sold.
The next criteria is the Origin of Manufacture, which must be set to Total, country of manufacture. Once complete, the formula should now look as follows:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[CarSalesTotalValues]","[CarSales].[UOM].[Units]","[CarSales].[Origin of manufacture].[Total, country of manufacture]")Step 8 – Apply filter cells to the CUBEVALUE() function.
In the prior example, the cell values never changed. The unit of measurement will always be Units, and the country of manufacture will always be the total. But what about in cells where the value can change, like the region?
Fortunately, the value for the filter can be taken from a cell value. We will test this with the GEO (Region) column first.
When you get to the point where you have added the left bracket to fill in the filter value, instead insert a quotation mark.
Then, you can add in a & sign, the cell reference (in this case, with $ notation, since it will not be changing as the formula is copied), and another & sign. Finally, create another quotation mark, right bracket and a quotation mark to close the formula. Your GEO filter should look as follows:
"[CarSales].[GEO].["&$B$4&"]"The formula now shows the filter with the GEO filter applied.
Next, apply similar criteria to the month and vehicle type using the following filters:
For date:
"[CarSales].[REF_DATE].["&$A8&"]"For vehicle type:
"[CarSales].[Vehicle type].["&B$6&"]"Once this is complete, the entire formula should look as follows:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[CarSalesTotalValues]","[CarSales].[UOM].[Units]","[CarSales].[Origin of manufacture].[Total, country of manufacture]","[CarSales].[GEO].["&$B$4&"]","[CarSales].[REF_DATE].["&$A8&"]","[CarSales].[Vehicle type].["&B$6&"]")This will show the correct value now in cell B8.
The formula that has been set up for January can now be copied on to every month for both passenger cars and trucks. Once this has been completed, the next step is to add the totals.
Step 9 – Aggregating quarterly values with SUBTOTAL().
While it might be tempting to use a SUM() function to add up the quarterly values, it is better to use a SUBTOTAL(9,[Cells]) formula, like the one seen below.
=SUBTOTAL(9, B8:B10)This formula can be copied into every quarter in columns B and D.
Once this is complete, a SUBTOTAL() can be added for all months in column B and D (including the prior subtotals), which reduces the risk of a quarter not being properly captured.
=SUBTOTAL(9, B8:B23) At this point, your report will look as follows:
Step 10 – Using a SUM() for total units.
While you could filter the CUBEVALUE() to get the value for “Total, new motor vehicles”, it is easiest to calculate the total by just summing up the passenger cars and trucks columns.
=SUM(B8,D8)Next, copy that formula down from row 8 to row 24, and you should have everything ready except the percentages.
Step 11 – Fill percentages by dividing months by columns.
Finally, you need to fill in the percentage of the total for both passenger cars and trucks.
This percentage is added by dividing the units column by the total column, making sure to use absolute referencing for column F.
=B8/$F8Once these percentages are copied for all cells, your report is complete! Congratulations!
Next Steps
There are still some ways that the table can be improved, such as adding new fields to the underlying data model and adding a new function for the percentage of total.
But for now, this completes the introduction of methods that can be used to build a quarterly report in Excel.