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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrscaletta
Helper II
Helper II

SUM GROUP BY PERSON and LABOR DAY

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:

BI1.PNG

** 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:

 

BI2.PNG

 

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

1 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 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing 

 

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

 

The dashboard, now, shows this:

BI_SampleTask.PNG

 

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 🙂

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 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

It works!! Thanks @CheenuSing 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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