Forum Discussion

JLambs20's avatar
JLambs20
Icon for Helper III rankHelper III
2 years ago
Solved

Create a Matrix from two tables that includes Measures and a "Target" column

Hello!

 

I need to create a matrix in BI where the row headers are the department, the columns are the numerical representations of the months of the year (ie., Jan = 1, Feb = 2, etc.), but the last column needs to say "Target".  The values for the months are a measure/calculation whereas the Target column would show the target value from a separate table.  Here's a representation of what the first table would show:

Table 1:

Dept Month Year Time
Worked		Hours Billed
A1202387
A2202398
B12023106
B2202394


Table 2: 

Dept Year Plan Target
A 2023 Plan60%
B 2023 Plan80%


Final Result would look something like this:

Year2023 
Dept123456789101112Plan Target
A88%89%75%65%85%95%65%48%35%87%88%95%60%
B60%44%50%64%56%45%87%24%65%86%57%79%80%

 

I have no problems creating a measure for the values of the table (from Table 1: Hours Billed / Time Worked) but I don't know how to pull in that target number from Table 2 and also show the column header as "Plan Target".  


Any assistance would be greatly appreciated!

Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    Hi JLambs20 

     

    Would something like the following work?

     

    In Power Query:
    - Create a Period custom column (=Text.From([Month]) & "/1/" & Text.From([Year]))    (Your format might be different.)
    - Change type to date
    - Remove [Year] and [Month] columns
    - I also renamed the queries to ‘Fact-Table1’ and ‘Dim-Table2’.
    Exit Power Query

     

    I created a simple DAX Date table and related it to ‘Fact-Table1’. Also, relate ‘Fact-Table1’ and ‘Dim-Table2’ on [Dept].

     

    Create the following measure and format as Percentage.

     

    Pct = 
    IF(
        HASONEVALUE( 'Date'[MonthNo] ),
        AVERAGEx(
            'Fact-Table1',
            DIVIDE( [Hours Billed], [Time Worked] )
        ),
        AVERAGE( 'Dim-Table2'[Plan Target] )
    )

     

     

    Create a matrix and populate as follows:
    Rows: ‘Dim-Table2’[Dept]
    Columns: ‘Date’[MonthNo]
    Values: [Pct]

     

    In the Format pane under Column Subtotals, change the Subtotal Label to “Plan Target”.

     

    Let me know how it goes.

     

    JLambs20.pbix

     

1 Reply

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    Hi JLambs20 

     

    Would something like the following work?

     

    In Power Query:
    - Create a Period custom column (=Text.From([Month]) & "/1/" & Text.From([Year]))    (Your format might be different.)
    - Change type to date
    - Remove [Year] and [Month] columns
    - I also renamed the queries to ‘Fact-Table1’ and ‘Dim-Table2’.
    Exit Power Query

     

    I created a simple DAX Date table and related it to ‘Fact-Table1’. Also, relate ‘Fact-Table1’ and ‘Dim-Table2’ on [Dept].

     

    Create the following measure and format as Percentage.

     

    Pct = 
    IF(
        HASONEVALUE( 'Date'[MonthNo] ),
        AVERAGEx(
            'Fact-Table1',
            DIVIDE( [Hours Billed], [Time Worked] )
        ),
        AVERAGE( 'Dim-Table2'[Plan Target] )
    )

     

     

    Create a matrix and populate as follows:
    Rows: ‘Dim-Table2’[Dept]
    Columns: ‘Date’[MonthNo]
    Values: [Pct]

     

    In the Format pane under Column Subtotals, change the Subtotal Label to “Plan Target”.

     

    Let me know how it goes.

     

    JLambs20.pbix