topic Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values in DAX Commands and Tips

HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 10:00:18 GMT

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?

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 11:06:43 GMT

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 11:18:52 GMT

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.

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 11:37:44 GMT

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 11:41:41 GMT

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.

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 11:48:53 GMT

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 12:02:03 GMT

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?

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 12:11:36 GMT

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

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 12:28:03 GMT

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)

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 14:22:58 GMT

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

Anonymous — Tue, 19 Jul 2022 14:25:16 GMT

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

Re: HELP: Matrix Column Subtotals Type different Summarization Type from Values

tamerj1 — Tue, 19 Jul 2022 14:31:35 GMT

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