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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thankyou yingyinr, thankyou very much.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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