Forum Discussion
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 |
| 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?
Hi Anonymous
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkoooCount = IF ( ISINSCOPE (entities[entity] ), DISTINCTCOUNT ( relationships[investor] ), DISTINCTCOUNT ( relationships[entity] ) )
11 Replies
Hi Anonymous
Here is the updated solution as per your requirement https://www.dropbox.com/t/4TYzPYWvbgrSkoooCount = IF ( ISINSCOPE (entities[entity] ), DISTINCTCOUNT ( relationships[investor] ), DISTINCTCOUNT ( relationships[entity] ) )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 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.
Hi Anonymous
Sorry my mistake. the 2nd matrix was wrong. Please double check and confirm which oneThis
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
Please try this solution https://www.dropbox.com/t/LMuorjaEkk8Qx4uSCount = IF ( ISINSCOPE (entities[entity] ), COUNTA ( relationships[investor] ), DISTINCTCOUNT ( relationships[investor] ) )
Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/dhzv2jqUxI6rL3TaPlease let me know if this is what you are looking for. Thank you
Anonymous
Here is the solution with correct grand total https://www.dropbox.com/t/On3l9e4jULU2pm5BCount = SUMX ( SUMMARIZE ( relationships, entities[entity], relationships[investor] ), CALCULATE ( IF ( ISINSCOPE (entities[entity] ), DISTINCTCOUNT ( relationships[investor] ), DISTINCTCOUNT ( relationships[entity] ) ) ) )