Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
matrix
subtotals
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    Count = 
IF ( 
    ISINSCOPE (entities[entity] ),
    COUNTA ( relationships[investor] ),
    DISTINCTCOUNT ( relationships[investor] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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)