# 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….

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Power Query: How To Merge Tables W/Different Columns

In this post, I'm going to show you how to do a conditional merge of tables in power query. For this...

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Dashboard Design – An Impressive Page Turning Visualization Idea

Power BI Dashboard Examples In this tutorial, I want to highlight one of the most innovative...

## Power BI Visual – Showcase Customer Purchase Dates

In this blog, I’ll share a great Power BI visual tip. I'll show you how to dynamically visualize things...

## Power BI DAX Measures For Events In Progress

In this tutorial, I'm going to show you how to create Power BI DAX measures for events in progress....

## Small Multiples Chart In Power BI: An Overview

In this tutorial, we’ll talk about the small multiples chart, which is a new preview feature introduced...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX Measure Analysis: Breaking Down Long DAX Measures

In this tutorial, I'm going to show you some detailed DAX measure analysis on how I put together one of...

## Business Themed Power BI Dashboard – Power BI Online Service

This is a quick tutorial about how you can make your Power BI dashboard and app more presentable and...

## Understanding Evaluation Context in Power BI

The most important concept in understanding DAX is context. There are three main types of context: the...

In this blog post, we'll continue our series on planning for Power BI projects. Just to refresh your...

## Extracting A Query Result In DAX Studio Using The Output Option

In this tutorial, you'll learn how to extract a query result from measures and tables in DAX Studio,...