Forum Discussion

Pbiuserr's avatar
Pbiuserr
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

How to achieve such table

Hello I have table with project hours and table with hours outside the project. I'd like to produce a final table like in the capture. I've put only columns mandatory for that, there are plenty in b...
  • PaulDBrown's avatar
    3 years ago

    Here is one way.

    First add a new column to each table to establish the "Type":

    Add a new column to the outside hours table for "project":

    Create dimension tables for both employee and type following this pattern:

     

    Employee table = 
    DISTINCT(
        UNION(
        VALUES('On Project Table'[Emp ID]), VALUES('On Project Table'[Emp ID])))

     

    Create a dimension table for project using:

     

    Project Table = 
    ADDCOLUMNS(
        DISTINCT(
            UNION(
                VALUES('On Project Table'[ProjectDsc]), VALUES('Out of hours Table'[Project]))),
                "Project", IF([ProjectDsc] = "No Project", BLANK(), [ProjectDsc]))
               

     

    Set up the model as follows

    Create a measure for the hours:

     

    Sum Hours = 
    SUM('On Project Table'[Hours on Project]) + SUM('Out of hours Table'[Hours outside project])

     

    Set up a table visual using the fields from the dimension tables and the measure to get:

     

    Sample PBIX file attached