Skip to content
Carmel Eve By Carmel Eve Software Engineer I
Quick tip – Removing totals from a matrix in Power BI

(TL;DR – Create a measure which calculates the SELECTEDVALUE and use that rather than the table column to create the matrix)

We have recently been doing a lot of work with Power BI. In the process we came across a few things we thought it was worth sharing. The first of these is a quick tip on how to remove summarisation from columns in a matrix.

So, say we had data that looks like this:

Raw data

If we then put that data into a matrix we get:

Matrix data

Clearly the favourite colour column should not be being summarised. The issue is that the way that the matrix is being generated is that each row is applying a filter context – e.g. [Name] = 'Ammar Legge'. The favourite colour column is then just taking the first colour it finds. This works for each of the normal rows because there will only ever be a single row from the original table returned by each filter context.

Power BI Weekly is a collation of the week's top news and articles from the Power BI ecosystem, all presented to you in one, handy newsletter!

However, for the total column the same calculation is being carried out without any filter on name, and we are therefore just returning the colour which is first in the alphabet.

My first thought was that setting this:

Data summarization

On the favourite colour column would fix the problem. However, the matrix isn't using the default summarisation to calculate the values so this has no effect.

Discover your Power BI Maturity Score by taking our FREE 5 minute quiz.

Instead we need to create a measure:

Creating a measure

And use that instead of the table column to create the matrix:

Using the measure to create a matrix

This will then calculate the SELECTEDVALUE for each row, which will result in a BLANK when there isn't a single value for the column in the current filter context.

Carmel Eve

Software Engineer I

Carmel Eve

Carmel is a software engineer, LinkedIn Learning instructor and STEM ambassador.

Over the past four years she has been focused on delivering cloud-first solutions to a variety of problems. These have ranged from highly-performant serverless architectures, to web applications, to reporting and insight pipelines and data analytics engines.

In her time at endjin, she has written many blog posts covering a huge range of topics, including deconstructing Rx operators and mental well-being and managing remote working.

Carmel's first LinkedIn Learning course on how to prepare for the Az-204 exam - developing solutions for Microsoft Azure - was released in April 2021. Over the last couple of years she has also spoken at NDC, APISpecs and SQLBits. These talks covered a range of topics, from reactive big-data processing to secure Azure architectures.

She is also passionate about diversity and inclusivity in tech. She is a STEM ambassador in her local community and is taking part in a local mentorship scheme. Through this work she hopes to be a part of positive change in the industry.

Carmel won "Apprentice Engineer of the Year" at the Computing Rising Star Awards 2019.

Carmel worked at endjin from 2016 to 2021.