# How To Create Unique Subtotals In Power BI Tables

In this tutorial, Iâ€™m going to show you how to create unique subtotals in your Power BI tables using the SWITCH TRUE logic. You may watch the full video of this tutorial at the bottom of this blog.

This is a breakout session from a financial reporting course released by Enterprise DNA.

The session includes a detailed application of a variety of financial reports such as income statements, balance sheets, and cash flow statements.

This technique focuses on how you can create generic template designs and subtotals.

General tables in Power BI don’t give you enough flexibility. Theyâ€™re very limited in terms of creating unique custom designs or summary calculations.

Thus, this tutorial will walk you through the methods you can use to create subtotals like Total Revenue, Total Cost of Goods Sold, and Total Profits, along with how to place them into your preferred format in your reports.

## Creating A Template In Excel

The first thing you need is to set up a template containing your subtotals in Microsoft Excel.

For this example, you can see that thereâ€™s Total Revenue, Cost of Goods Sold, and Gross Profit Percent:

You need to make sure that the calculations are in the correct spots in the template table.

This is the data model used for this example:

It contains the financial calculations and relationships used to create the report. Itâ€™s just a matter of allocating this raw data into a specific template.

## Formulas Used For Subtotals

You need to create unique measures for each subtotal in Power BI.

For the Revenue, you need to have a separate measure which calculates the subtotal amount:

A different measure is also used to calculate the Cost of Goods Sold:

This is the formula for the Total Gross Profit:

Once youâ€™ve created the subtotals in Power BI, you must allocate these to the correct rows in your template table.

## Using The SWITCH TRUE Logic

You need to use the SWITCH TRUE logic to allocate the subtotals in specific rows within the report.

This formula logic is better compared to using nested IF statements.

For every single row in the table, itâ€™s trying to work out the current row that you are in. If it matches, it will evaluate to TRUE, and will then input the subtotal measure to that row:

The DIVIDE function is simply used to reduce the figure from millions to thousands; itâ€™s easier on the eyes. This portion in the formula will depend on the measures you have.

If youâ€™re in a subtotal row, the formula will place the subtotal measure there. It will do that for all the subtotals youâ€™ve created.

You can use this to create any unique table. All you have to do is use SWITCH TRUE to allocate the measures to a specific row in a table.

For those rows that arenâ€™t subtotals, all you need to use is a SUMIF formula in Microsoft Excel.

If an amount doesn’t belong to any subtotal, this part of the formula will work out its row in the table and then allocate the calculation to that row.

## Conclusion

To summarize, you first need to calculate your subtotals in a template and then use SWITCH TRUE to allocate them to their respective rows in the Power BI table.

When you compare it to nested IF statements, the SWITCH TRUE logic simplifies the formula and makes it easier to understand.

The technique used in this tutorial is also reusable. You can use it to create any type of unique table in Power BI.

If you want to review the full course module, check out the Enterprise DNA website.

All the best,

Sam

***** Related Course Modules *****
Financial Reporting W/ Power BI
Scenario Method Workshops
DAX Formula Deep Dives

***** Related Support Forum Posts *****
Financial Reporting In Power BI – Custom Subtotals
Finance Template And Dozens Of Subtotals And Totals
DAX For Dynamic Subtotals & Grand Total

For more subtotal support queries to review see hereâ€¦.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...