Removing the Total Value for a Column in the Table Visual of Power BI Using ISFILTERED

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Table visual is one of the most commonly used visuals. In Power BI, you can turn off the total row (when it won’t make sense to have the total) entirely. However, you cannot turn off the total for some columns and keep it working for the others. Using a DAX function, you can, however, do this easily. Let’s see how it is possible.

Total Row in the Table Visual

In the table visual in Power BI, you get the total row by default;

You can disable the total row entirely using the Format tab of the visual, and then under the Total;

There are scenarios that you want to only turn off the total value calculation for one of the columns. turning off the entire total row might not be the best option in those cases. For example, the total for SalesAmount in the above example is good to have, but the total for the List of FullName values is not necessary. The List of FullName values is showing the three custom names under each category, and when we are in the total row, that means everyone. So it is better to get it removed. I show you two ways of removing this total value.

The DAX Way: ISFILETERED

There is a very useful function in DAX that can help you to determine if the value showed in a visual, filtered by a field/table or not. The function is called IsFiltered. This DAX function, get the field/table name as the input and returns a true or false value, meaning that if the context in which the calculation is running, is filtered by that field or not.

ISFILTERED(<table or column name>)

To show you how this function works, I write a measure like below:

If I add this into my table visual, I can see when this function returns TRUE or FALSE

Because the table visual is filtered by the EnglishEducation field in the above screenshot, the ISFILTERED returns true for every row in the table. However, the total row is NOT Filtered by the EnglishEducation, and that means returning False.

The ISFILTERED function is very useful in many scenarios of writing DAX expressions. In our case, for removing the total value from a specific column, it can be used simply by using the field that filters all other fields. In my case, All I need is to check if the EnglishEducation is filtered or not and then use it in the measure calculation of “List of FullName values” (this measure is, by the way, a quick measure created by a concatenated list of values.)

Here is my changed version of the DAX expression with ISFILTERED in it;

As you can see, I wrapped the measure’s value inside an IF statement like this:

This would give me something like this:

The above IF statement is only using the expression, and the what-if true, part of the IF. the what-if false part of it is not used, because it is optional, and when not provided, the default is blank.

If you ever want to provide something else as the result for the total row, you can then use the what-if false part of the if statement like below;

in my case, I change it to All Customer text;

And this is what the results look like now:

Formatting Visual Way; The Easy Way, but Limited

If you are just looking for removing the total for a specific column, and you don’t want to spend time fixing your DAX codes, there is an easier way. You can go to the Format tab of the visual, Under Field Formatting, select the field that you don’t want the total for it, and then make the below changes:

Set the Apply to values to Off, Apply to total to On, and then set the Font color to white (or something that makes the total looks blank)

This method of visual formatting is easier, However, it comes with caveats;

  • If your Field values have specific formatting, then this formatting might change that.
  • If you are using themes or any other background colors, you need to configure the Font color and Background color respective to that after every change in the theme.
  • You cannot replace the total value with a different text or something like what you have seen in the DAX way of doing it.

Summary

I’m sure at some points, there will be an option in the table visual to turn off the total for each field. However, until that time, this might help you to do what you want. On the other hand, this helped you to understand how the ISFILTERED DAX function works, and use it in countless other scenarios in your Power BI solution.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “Removing the Total Value for a Column in the Table Visual of Power BI Using ISFILTERED

Leave a Reply

%d bloggers like this: