Forum Discussion

markefrody's avatar
markefrody
Post Patron
3 years ago
Solved

1 Table/Matrix Visual for Calculated Columns and Data from Another Table

Hi,

 

I want to create one table/matrix visualization using calculated columns in table 1 and data from another table (table 2).

 

The output of the table/matrix visualization would look like this:

Any help that you can provide in creating this table/matrix would be greatly appreciated.


Thanks and best regards,
Mark V
 

Need Help
  • selimovd's avatar
    selimovd
    3 years ago

    Hey markefrody ,

     

    you could use a measure that is checking for the current department and then returning the value from the desired column:

    Measure = 
IF( 
    HASONEVALUE('Table 1 Actual'[Department]),
    SWITCH(
        MAX( 'Table 1 Actual'[Department] ),
        "Finance", SUM( 'Table 1 Actual'[Finance Urgent Measure]),
        "HR", SUM('Table 1 Actual'[HR Non-Urgent Measure]),
        "Operations", SUM( 'Table 1 Actual'[Operations Urgent Measure] )
    )
)

     

    However, I personally wouldn't do a calculated column.
    You could also solve that in only one measure:

    Sum Measure =
CALCULATE (
    SUM ( 'Table 1 Actual'[Score] ),
    KEEPFILTERS (
        ( 'Table 1 Actual'[Department] = "Finance" && 'Table 1 Actual'[Type] = "Urgent" )
        || ( 'Table 1 Actual'[Department] = "HR" && 'Table 1 Actual'[Type] = "Non-Urgent" )
        || ( 'Table 1 Actual'[Department] = "Operations" && 'Table 1 Actual'[Type] = "Urgent" )
    )
)

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic
     

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    3 years ago

    Hey markefrody ,

     

    can you provide a sample file?

    Your calculated columns look more like rows, so I'm not sure how the data looks.

     

    Best regards

    Denis

    • markefrody's avatar
      markefrody
      Post Patron
      3 years ago

      Hi selimovd 


      It looks like this:


      I'm using DAX for that calculated column:

      Hope this helps. If not, please let me know. Thanks.

      Best regards,
      Mark V

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      3 years ago

      Hey markefrody ,

       

      you could use a measure that is checking for the current department and then returning the value from the desired column:

      Measure = 
IF( 
    HASONEVALUE('Table 1 Actual'[Department]),
    SWITCH(
        MAX( 'Table 1 Actual'[Department] ),
        "Finance", SUM( 'Table 1 Actual'[Finance Urgent Measure]),
        "HR", SUM('Table 1 Actual'[HR Non-Urgent Measure]),
        "Operations", SUM( 'Table 1 Actual'[Operations Urgent Measure] )
    )
)

       

      However, I personally wouldn't do a calculated column.
      You could also solve that in only one measure:

      Sum Measure =
CALCULATE (
    SUM ( 'Table 1 Actual'[Score] ),
    KEEPFILTERS (
        ( 'Table 1 Actual'[Department] = "Finance" && 'Table 1 Actual'[Type] = "Urgent" )
        || ( 'Table 1 Actual'[Department] = "HR" && 'Table 1 Actual'[Type] = "Non-Urgent" )
        || ( 'Table 1 Actual'[Department] = "Operations" && 'Table 1 Actual'[Type] = "Urgent" )
    )
)

      If you need any help please let me know.
      If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

      Best regards
      Denis

      Blog: WhatTheFact.bi
      Follow me: twitter.com/DenSelimovic