HELP: Matrix Column Subtotals Type different Summarization Type from Values

[Anonymous]

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?

[tamerj1]

@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] )
        )
    )
)

[tamerj1 · Accepted 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]

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

[tamerj1]

@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

[Anonymous]

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]

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.

[tamerj1]

@Anonymous 
Please try this solution https://www.dropbox.com/t/LMuorjaEkk8Qx4uS

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

[Anonymous]

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)

[tamerj1]

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

[Anonymous]

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]

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