Subtotal Counters within Groups and Subgroups for Calculated Columns

Very often there is a need to calculate counts of various elements within groups or subgroup. This is very useful for calculating ratios. They key element of a ratio is to get the right denominator within desired filter context.

Here I will describe the easy way to get any desired count of a group or a subgroup in the calculated column that could be later used for the ratio’s denominator.

Using Contoso DB, lets say, we have a Customer Table that has attributes of Customer Names, Geography, Marital Status, Gender and Yearly Income. The actual table has over 18,000 rows, the screenshot below is an excerpt of it.

Our goal is to calculate the count of:

1. Total Amount of Customers
2. Number of Customers per Country
3. Number of Customers per Country and Gender
4. Number of Customers per Country and named John
5. Number of Customers per City that have Yearly Income above $100,000

To do that in PowerBI, is quite simple. We have to use a combination of COUNTROWS, FILTER and EARLIER functions.

1. To get the Total Amount of Customers, since we are in the Customer dimension table where there is a unique number of customers, we do the following:

2. To get the number of Customers per Country, we need to do the following:

We start with COUNTROWS like previously. Next, we need to count number of customers within Country groupings. To do that we use FILTER and EARLIER function that counts the customer within Countries as stated above.

3. To get a count of multiple Subgroup element, such as count the number of Customers within a Country and Gender we just use same formula as above, but also add “&&” or AND element to extend the filters. (Note that FILTER function is very flexible and we can use as many “&&” as we like to extend the filter to many subgroups)

4. To get a count of customers per country and named John, we do similar as above, but in the last part of the filter we equate the FirstName Column to “John”

5. To get a count of customers per City with Yearly Income above 100,000 we follow same process, but only at the end add “>” condition

Finally we have made all the desired Totals and Group Subtotals and our PowerBI table excerpt looks like this:

Now we are ready to perform further calculations using those denominators to form new ratios. We can either calculate directly in the table by adding new Calculated Columns or create Measures that do SUMX or similar iterators over this table using those denominators. We can also create a new Virtual Table summarizing it by the key columns such Country, City, Income, Gender and our new Denominators.

I will write another post on ratios and allocation spreads.