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

[markefrody]

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
 

[markefrody]

@selimovd 
Below is a similar sample file

https://www.dropbox.com/s/34tv3pumrnn8e81/Sample%20File.pbix?dl=0

Current table visual looks like this:

I would like to have this output:

Best regards,
Mark V

[selimovd · Accepted solution]

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
 

[markefrody]

@selimovd Thanks Dennis. It works! 

[selimovd]

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]

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