How to display hours worked in a month from multiple tables in the same Matrix (Has Pbix file)

[PowerBiNoob37]

Hi,

I wish to display the amount of hours in total my employees have worked in a month as well as display the different types of tasks they achieved in the same matrix visual from two different sources using a date table on a slicer.

My data in the EmployeeHoursTable looks like the following

My data in the TypeTable looks like the following

I have created a date table by the following and set the many to one relationship

CalanderTable = CALENDARAUTO()

However i can not get the hours column to display with the type on the columns i.e here is what i have where as i want the hours to be displayed and summed for the month as another column next to the type?

Could anyone help? Here is my pbix file

https://1drv.ms/u/s!As_sc_x3g6_qkGd3TzYM3_qphOVh

[Anonymous]

Hello @PowerBiNoob37 

You don't have similar dates in the Type and Employee table.
As you can see we've only 202104-202108 in the Type table but in the Employee table we've 202109-202111. Also, please change the date type as Date in Employee and Calendar table because in the Type table there is only Date not Date and time.

[PowerBiNoob37]

That doesnt help me sorry, now if i add them it doesnt add the hours like i want, im trying to produce exactly this but in a matrix visual i can sort via a date slice.

Month	int	nly	ety	Hours Worked
Jan-21	2			300
Feb-21		1	1	200
Mar-21			2	200

.

I cannot seem to add hours without it looking like

I want it too look like (MS Painted) I can change the values as this is just test data, the values wont line up exactly, so in my case the count types would be empty and the hours would be in it,

[v-yiruan-msft · Accepted solution]

Hi @PowerBiNoob37 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Change the data type of field Date in table CalanderTable as Date

2. Delete the relationship between EmployeeHoursTable and CalanderTable

3. Create two measures as below to get the worked hours

Measure = 
VAR _selyear =
    SELECTEDVALUE ( 'CalanderTable'[Date].[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'CalanderTable'[Date].[MonthNo] )
VAR _hours =
    CALCULATE (
        SUM ( 'EmployeeHoursTable'[Hours] ),
        FILTER (
            'EmployeeHoursTable',
            YEAR ( 'EmployeeHoursTable'[DateWorked] ) = _selyear
                && MONTH ( 'EmployeeHoursTable'[DateWorked] ) = _selmonth
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'CalanderTable'[Date].[Year] )
            && ISINSCOPE ( 'CalanderTable'[Date].[Month] )
            && ISINSCOPE ( 'TypeTable'[Type] ),
        BLANK (),
        _hours
    )

Worked Hours = 
SUMX (
    GROUPBY (
        'CalanderTable',
        'CalanderTable'[Date].[Year],
        'CalanderTable'[Date].[Month]
    ),
    [Measure]
)

4. Toggle the option "Word wrap" off and adjust the width of Values fileld [Worked Hours] to make it invisible manually 

Best Regards

[PowerBiNoob37]

Thankyou yingyinr, thankyou very much.