Question

• How can I total up my values in BI?
• How can I add a subtotal or grand total to my report in BI?

Grand Total

In report tables, the Grand Total for a field will provide either the sum (average, maximum, minimum) of all values or count (count distinct) values within that field across the entire data source.

For example, in a report for Northwind database’s Orders table, the Grand Total for Freight field will tell the sum of all Freight costs until now.

To add a Grand Total to a column/field:

1. Navigate to the report in BI and click on Edit > Design
2. Within the configuration mode, click on the column you want to see a grand total of
3. Make sure you are under the Field Properties tab on the right-hand field properties menu
4. Then click the gear icon next to Grand Total
   
   
   
5. In the Grand Total settings window, enter a title for your Grand Total (ex: Grand Total Estimates) 
6. Next, select the appropriate Grand Total function option from the dropdown. See this manual for more detail on what each calculation does. Typically, you will be utilizing SUM, but other calculations can be used as needed.
7. Then choose the format you would like for the Grand Total (dollar, number, etc)
8. Click OK
9. Switch to preview mode to ensure the total looks the way you would like

Important Note: Grand Total requires a name, if you have multiple columns you are applying a Grand Total to, you may see them broken up into two rows (like the image below), if you want these to be on the same line/row, make your Grand Total title (see step #5 above) the same (ie Grand Total).

Subtotal

In report tables, the Sub Total for a field will provide either the sum (average, maximum, minimum) of all values or count (count distinct) values within that field for each separated group without having any sub-report.

For example, in a report for Northwind database’s Orders table, to have the sum for all Freight costs to each country without having to create additional reports, Sub Total can be used.

To add a Subtotal to a column/field:

1. Navigate to the report in BI and click on Edit > Design
2. Within the configuration mode, click on the column you want to see a subtotal of
3. Make sure you are under the Field Properties tab on the right-hand field properties menu
4. Then click the gear icon next to Subtotal
   
   
   
5. Select the appropriate Subtotal function option from the dropdown. See this manual for more detail on what each calculation does. Typically, you will be utilizing SUM, but other calculations can be used as needed.
6. Then choose the format you would like for the subtotal (dollar, number, etc)
7. Click OK
8. Switch to preview mode to ensure the total looks the way you would like

Questions? Contact Us!