Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PowerBiNoob37
Helper I
Helper I

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

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

 

PowerBiNoob37_0-1636512964825.png

 

My data in the TypeTable looks like the following

 

PowerBiNoob37_1-1636513028740.png

 

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?
 
PowerBiNoob37_2-1636513176785.png

 

Could anyone help? Here is my pbix file

 

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

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

yingyinr_2-1637202999462.png

2. Delete the relationship between EmployeeHoursTable and CalanderTable

yingyinr_1-1637202952925.png

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 

yingyinr_4-1637203312960.png

yingyinr_3-1637203171495.png

Best Regards

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

TarunSharma_0-1636529482805.png

TarunSharma_1-1636529494678.png

 

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.

 

MonthintnlyetyHours Worked
Jan-212  300
Feb-21 11200
Mar-21  2200

.

I cannot seem to add hours without it looking like

 

PowerBiNoob37_0-1636584750559.png

 

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,

 

PowerBiNoob37_1-1636584977888.png

 

 

Anonymous
Not applicable

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

yingyinr_2-1637202999462.png

2. Delete the relationship between EmployeeHoursTable and CalanderTable

yingyinr_1-1637202952925.png

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 

yingyinr_4-1637203312960.png

yingyinr_3-1637203171495.png

Best Regards

Thankyou yingyinr, thankyou very much.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.