SUM GROUP BY PERSON and LABOR DAY

[jrscaletta]

Hi all.

I have this structure:

CALENDAR table:
Date
Labor Day: Shows 1 if is labor day

TASK table:
Userid
Task
Date
Hours

USERS table:
Userid
Username

RESERVEDTIMEbyUser TABLE
Userid
Expected hours:  by labor day

Relationships:

CalendarTable.Date = TaskTable.Date (1:N)

TaskTable.Userid = UsersTable.Userid (N:1)

ReservedtimebyuserTable.userid = UsersTable.Userid (1:N)

RESERVEDTIMEBYUSERTABLE is like this:

** Table TASK and USERS contain more much users than RESERVEDTIMEBYUSERTABLE.

Then, when I make a measure like SUM(Expecte hours), it returns a table for all users and the total for each one:

Then I have a filter by date, that counts labour day. Then, how can I obtain the expected hours by user and labour day?

Thanks in advance

[CheenuSing]

Hi @jrscaletta ,

Please post some data and your pbix in the shared  OneDirve or Google Drive and paste the link here to find a solution.

What is output expected of the data posted ?

Cheers

CheenuSing

[jrscaletta]

Hi @CheenuSing 

Thanks for your reply. I'm going to to explain better and attach PBIX sample file.

The dashboard, now, shows this:

As you can see, I have 261 labors day selected by date slice.

Then, in "PrevisionDedicacion" table, I defined that for the user1, the expected hours by labor day are 3,20.

If I've selectec 261 labor day, how can I show this? (3.20 * 261) for each user?

Thanks in advance 🙂

[CheenuSing · Accepted solution]

Hi @jrscaletta ,

After going through your pbix. I did the following

1. Created a measure

                                    TotLabourDay = SUM(Calendario[LaborDay])

2. Replaced the field in the Card Visual with this.

3.  Created another measure

TotByUser = CALCULATE(

            SELECTEDVALUE(PrevisionDedicacion[Expected h support]) *

                         [TotLabourDay]

                   )

Added this measure to your table visual.

4. In the table visual set the visual filtering to Hours is Not blank

Cheers

CheenuSing 

[jrscaletta]

It works!! Thanks @CheenuSing