[PowerBI Drops] Advanced Table Customization: Column totalizers

Get full control of operations in tables by customizing column totalizers

PowerBI is the leading visualization, interactive reporting, and dashboard tool used by small, medium, and large organizations. The out-of-the-box features already meet most of the needs for building interactive visualizations.

However, in some cases, the demands are more specific and are not always met by the no-code settings of the tool. This post will demonstrate how we can apply specific transformations to the PowerBI table look.

Imagine the following case: we have the price charged in different years (2021 and 2022), and we need to calculate the inflation between prices, but in the totalizer row, the stakeholders would like to see the inflation between product totals.

We can start by creating a measure to perform the inflation calculation, using the following DAX code:

Doing this, we get the following result using PowerBI’s native column totalizers:

Here we come to a point where we absolutely need to be able to custom manipulate the totalizer for the inflation column. The value we should have is not the sum of the inflation of the products but the inflation between the sum of the products.

To change this, an alternative is as follows:

• Calculate the sum of both periods — let’s call these values __TOTAL_A and __TOTAL_B
• Check the inflation of these total values — let’s call this value __TOTAL_INFL
• Calculate the inflation of the values line by line — let’s call this value __TOTAL_ROW
• Finally, tell PowerBI to use the values of __TOTAL_ROW and __TOTAL_INFL appropriately.

We can do this with the following formula:

Using the ISINSCOPE function, we can determine the position of the value (table row or totalizer row). Thus, we can arrive at the expected result using the SUMX function and storing the partial results in VAR variables.

I hope you enjoyed reading this post. If you did, consider following me on Twitter. Thank you for your time.

Take care, and keep coding!