Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

HELP: Matrix Column Subtotals Type different Summarization Type from Values

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
aUK5
bUSA3
cUK8
.........

 

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

X2017loan
aX2020grant
aY2020grant
bY2018loan
bY2019loan
cX2019grant
cX2021grant
cY2017grant
............

 

 

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
X123
Y1 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?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkooo

1.png

Count = 
IF ( 
    ISINSCOPE (entities[entity] ),
    DISTINCTCOUNT ( relationships[investor] ),
    DISTINCTCOUNT (  relationships[entity] )
)

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

@Anonymous 
Here is the solution with correct grand total https://www.dropbox.com/t/On3l9e4jULU2pm5B

1.png

Count = 
SUMX (
    SUMMARIZE (
        relationships,
        entities[entity],
        relationships[investor]
    ),
    CALCULATE (
        IF ( 
            ISINSCOPE (entities[entity] ),
            DISTINCTCOUNT ( relationships[investor] ),
            DISTINCTCOUNT (  relationships[entity] )
        )
    )
)
tamerj1
Super User
Super User

Hi @Anonymous 
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkooo

1.png

Count = 
IF ( 
    ISINSCOPE (entities[entity] ),
    DISTINCTCOUNT ( relationships[investor] ),
    DISTINCTCOUNT (  relationships[entity] )
)
Anonymous
Not applicable

Thank you very much - this has been bothering me for quite some time.

tamerj1
Super User
Super User

@Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/dhzv2jqUxI6rL3Ta

1.png2.png

Please let me know if this is what you are looking for. Thank you

Anonymous
Not applicable

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? 

Anonymous
Not applicable

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

1.png

Count = 
IF ( 
    ISINSCOPE (entities[entity] ),
    COUNTA ( relationships[investor] ),
    DISTINCTCOUNT ( relationships[investor] )
)
Anonymous
Not applicable

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
X122
Y1 1

 

Or this

investor  entity a   entity c  COLUMN SUBTOTAL
X112
Y1 1
Anonymous
Not applicable

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. 

tamerj1
Super User
Super User

Hi @Anonymous 
Which one is the correct result you are looking for?

This 

investor  entity a   entity c  COLUMN SUBTOTAL
X122
Y1 1

 

Or this

investor  entity a   entity c  COLUMN SUBTOTAL
X111
Y1 1

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.