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:

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

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.

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:

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.

Instead we need to create a measure:

And use that instead of the table column to create the 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 - is due for release next month. And 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.