Matrix shown bottom p38, topic 13
Matrix shown bottom p38, topic 13
[img][/img] I don't know how to post an image, so I'll describe it. There's an image of a matrix on the bottom of page 38 of "Supercharge Power BI" that shows the counts of occupations at various levels of yearly income. I don't understand what that "Total" of 5 is. For what it is worth, that's exactly the image I showed in my Power BI as I followed the book.
-
Jason Cockington
- Posts: 6
- Joined: Thu Jan 16, 2020 8:46 am
Re: Matrix shown bottom p38, topic 13
Hi Korndorb,
A matrix in Power BI applies filters on your data by the values represented in the rows.
So, when you look at the first row of the matrix, your Customers table gets filtered for YearlyIncome = 10,000, then your Count of Occupation runs a DISTINCTCOUNT on the filtered table, resulting in a total of 3. In this case, the three occupations are Clerical, Manual and Skilled Manual.
The same thing is happening for the row when YearlyIncome is 40,000. Now there are four occupations to distinctly count in the Customers table, Clerical, Management, Professional and Skilled Manual. Every value in a visual in Power BI is calculated this way.
In a matrix, each "cell" has a unique set of filters that differenciate it from the neighbouring "cells". Thus, when you get down to the Total row of the matrix, there is no filter on the Customers table coming from YearlyIncome, so the Customers table remains unfiltered. With no filters applied to the Customers table, all occupations are present in the Occupation column, and thus when the Count of Occupation runs a DISTINCTCOUNT on the column, all 5 occupations are counted.
The most important thing to understand about the Total rows in Power BI is that they are not the SUM of the values above them. A Total row asks "What is the value, when there are no filters applied?"
If you want to test this idea to help get your head around it, go into the data view of Power BI and select your Customers table. Then click on the [v] button on the YearlyIncome column and select an income. Then go to the Occupation column, and click the [v] button to see how many different occupations there are.
A matrix in Power BI applies filters on your data by the values represented in the rows.
So, when you look at the first row of the matrix, your Customers table gets filtered for YearlyIncome = 10,000, then your Count of Occupation runs a DISTINCTCOUNT on the filtered table, resulting in a total of 3. In this case, the three occupations are Clerical, Manual and Skilled Manual.
The same thing is happening for the row when YearlyIncome is 40,000. Now there are four occupations to distinctly count in the Customers table, Clerical, Management, Professional and Skilled Manual. Every value in a visual in Power BI is calculated this way.
In a matrix, each "cell" has a unique set of filters that differenciate it from the neighbouring "cells". Thus, when you get down to the Total row of the matrix, there is no filter on the Customers table coming from YearlyIncome, so the Customers table remains unfiltered. With no filters applied to the Customers table, all occupations are present in the Occupation column, and thus when the Count of Occupation runs a DISTINCTCOUNT on the column, all 5 occupations are counted.
The most important thing to understand about the Total rows in Power BI is that they are not the SUM of the values above them. A Total row asks "What is the value, when there are no filters applied?"
If you want to test this idea to help get your head around it, go into the data view of Power BI and select your Customers table. Then click on the [v] button on the YearlyIncome column and select an income. Then go to the Occupation column, and click the [v] button to see how many different occupations there are.
Jason Cockington is Self Service BI Trainer and Consultant with Excelerator BI
Return to “Help with the Book "Supercharge Power BI"”
Who is online
Users browsing this forum: No registered users and 1 guest