Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi Community,
Is there a way to change the type for matrix subtotals ?
Let me explain the issue I have. Imagine I have two tables
entities:
entity | country | value |
a | UK | 5 |
b | USA | 3 |
c | UK | 8 |
... | ... | ... |
and a relationship table, noting type of relationship( can be either grant or loan)and the year it started between entities and investors,
relationships:
entity | investor | year | type |
a | X | 2017 | loan |
a | X | 2020 | grant |
a | Y | 2020 | grant |
b | Y | 2018 | loan |
b | Y | 2019 | loan |
c | X | 2019 | grant |
c | X | 2021 | grant |
c | Y | 2017 | grant |
... | ... | ... | ... |
now I want to have a relationship matrix, where I can filter by country, relationship type and year and always show the TOP N entities in terms of "value" after the applied filter.
For filter "UK","grant", starting from 2018, I will get the following matrix. The values are an added column called "is_related" with all 1 added to the relationship table.
matrix visual:
investor | entity a | entity c | COLUMN SUBTOTAL |
X | 1 | 2 | 3 |
Y | 1 | 1 |
The problem is the following: I do not want to count the relationship between X and entity c twice. If I select the type of Matrix values to be COUNT (DISTINCT) the column subtotal however also changes to distinct counts and will be 1 for all rows that have a value.
I would want the column subtotal to be the sum of relationships for each investor. so in this case the subtotals should be 2 and 1 respectively. Any ideas how this could be solved?
Solved! Go to Solution.
Hi @Anonymous
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkooo
Count =
IF (
ISINSCOPE (entities[entity] ),
DISTINCTCOUNT ( relationships[investor] ),
DISTINCTCOUNT ( relationships[entity] )
)
@Anonymous
Here is the solution with correct grand total https://www.dropbox.com/t/On3l9e4jULU2pm5B
Count =
SUMX (
SUMMARIZE (
relationships,
entities[entity],
relationships[investor]
),
CALCULATE (
IF (
ISINSCOPE (entities[entity] ),
DISTINCTCOUNT ( relationships[investor] ),
DISTINCTCOUNT ( relationships[entity] )
)
)
)
Hi @Anonymous
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkooo
Count =
IF (
ISINSCOPE (entities[entity] ),
DISTINCTCOUNT ( relationships[investor] ),
DISTINCTCOUNT ( relationships[entity] )
)
Thank you very much - this has been bothering me for quite some time.
@Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/dhzv2jqUxI6rL3Ta
Please let me know if this is what you are looking for. Thank you
Sadly your solution has exactly the same issue I have. The total for investor X is 1, altough it should be 2 (investor X has a grant relationship with a and with c, so 2 in total) . Any idea how to solve that?
Hi @tamerj1 , thanks for the quick reply. What I am looking for is the first one. I want the subtotal to show the number of entries in each row.
@Anonymous
Please try this solution https://www.dropbox.com/t/LMuorjaEkk8Qx4uS
Count =
IF (
ISINSCOPE (entities[entity] ),
COUNTA ( relationships[investor] ),
DISTINCTCOUNT ( relationships[investor] )
)
I do not really care about the totals per entity. This shows how many relationship each entity has, but it still only shows that each investor has only 1 relationship in total, although X has two relationships (with a and c)
Hi @Anonymous
Sorry my mistake. the 2nd matrix was wrong. Please double check and confirm which one
This
investor | entity a | entity c | COLUMN SUBTOTAL |
X | 1 | 2 | 2 |
Y | 1 | 1 |
Or this
investor | entity a | entity c | COLUMN SUBTOTAL |
X | 1 | 1 | 2 |
Y | 1 | 1 |
The second one. I want an entry in the matrix if any relationship exists like a boolean (0 or 1) in this case and the subtotal to be the sum of relationships for each investor. Thanks so much.
Hi @Anonymous
Which one is the correct result you are looking for?
This
investor | entity a | entity c | COLUMN SUBTOTAL |
X | 1 | 2 | 2 |
Y | 1 | 1 |
Or this
investor | entity a | entity c | COLUMN SUBTOTAL |
X | 1 | 1 | 1 |
Y | 1 | 1 |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |