Matrix vs table

This is a dedicated forum for people that have questions about the book Supercharge Power BI. In the event of errors in the book, the error information will be in this forum.
Posts: 1
Joined: Fri May 14, 2021 12:39 pm

Matrix vs table

Postby Np503 » Fri May 14, 2021 1:16 pm

Hi there,
New to the forum and trying to get my head around an issue I'm facing. Using the examples from the book. I have a matrix with Sales[ProductKey] on rows and Products[Weight] on values. As expected this shows the same value for each row, however when I add Sales[ProductKey] and Products[Weight] into values on a table and select don't summarize I get an individual value for each row. Is this because a matrix applies some form of aggregation and a table is able just to show associated data through the primary/foreign keys?

The initial filter on the table/matrix is Sales[ProductKey] = 310. Sales is the data table so no filtering is applied to the Products lookup table. All of the unfiltered data from Products flows down to the Sales data table. Then weight is displayed. Weight isn't a measure so no calculation takes place but if the matrix tries to apply some form of aggregation it would make sense that all the values display the same. If this isn't the case I can't work out why a table displays individual values and a matrix doesn't :lol:


Posts: 1160
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Matrix vs table

Postby MattAllington » Fri May 14, 2021 2:50 pm

In the first example
Sales[ProductKey] on rows and Products[Weight] on values, the sales table doesn't filter the products table, hence the sum of weight is the same number you would get if you put weight in a card on its own - it is just the total of the weight column regardless of any filter on the sales table. So in this case, it is a summarisation - yes, but not as you suggested.

When you put both columns in a table with "don't summarise", you are effectively using both columns as filters (no summarisation). You can actually get the same behaviour from a matrix. Just put both Sales[ProductKey] and Products[Weight] into Rows in the matrix, then "expand all down 1 level" (the down pointing pitchfork icon in the top right corner of the matrix visual). You will see you get the same result as the table. When columns are set to "don't summarise" in a table, or when they are on rows/columns in a matrix, they are filtering. When the same column is set to summarise in a table, or are placed in the values section in a matrix, then they will summarise (somehow, depending on the setting). But in this case, with the columns you are referring to, the weight column cannot be filtered by the sales table, hence the reason it gives the total of the entire Product[weight] column.

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Posts: 1
Joined: Fri Aug 06, 2021 8:05 pm

Re: Matrix vs table

Postby NicolasFlamel » Fri Aug 06, 2021 8:12 pm

We also used matrices, but the tables are more convenient and you can use formulas
when developing our new project of online legal casino games with freespins we actively used arrays of formulas which made our life easier

Return to “Help with the Book "Supercharge Power BI"”

Who is online

Users browsing this forum: No registered users and 1 guest