Aggregated Calculations

[Vitaliy]

Hello everyone and sorry in advance for my English!
I would be very grateful for the help in this task:
At the entrance I have 3 tables with data (Table1, Table2, Table3).

Table1	  Table2				       Table3		
									
Name	  Date	    Mont_number  Spent Time  Name      Month_number  Capacity  isHolidays
A	  01.01.2018	1	 80	     A		1	     160	  0
B	  02.01.2018	1	 100	     A		1	     4	          1
C	  02.02.2018	2	 170	     A		2	     176	  0
D	  10.01.2018	1	 160	     B		2	     1	          1
	  10.02.2018	2	 180	     B		3	     168	  0
	  10.03.2018	3	 20	     C		3	     2	          1

With the help of links between these tables in the end, I want to find out the sum of "overtime" by all employees. For example, there is employee A, who in the month #1 worked 180 hours, and 160 were planned. In total, his overtime is 20 hours. If the employee worked <160 hours, then the processing would be equal to 0 (True Overtime).

After all the transformations, I got something like a table like ResultTable1, where TotalSpentTime is a column with data (without any my own calculations), and Capacity (wo Holidays) is a measure that I defined as follows: SUMX (FILTER (Table3; Table3 [isHolidays] = 0); Table3 [Capacity ]) * DISTINCTCOUNT (Table1 [Name]).

Result Table1

Name  Month_number TotalSpentTime  Capacity(wo Holidays)   Overtime	True_Overtime
A	1	    180	              160                     20	   20
A	2	    170	              176                    -6	           0
A	3	    0	              168                    -168	   0
B	1	    160	              160                     0	           0
B	2	    180	              176                     4	           4
C	3	    20	              168                    -148	   0

In the end, I want to get only one number - sum of overtimes: TotalTrue_Overtime = 20+0+0+0+4+0 = 24.

I would be very grateful for any help, thanks!

[AkhilAshok]

Try creating a Table4 which is at Month granularity and IsHoliday = 0 (using Power Query or DAX) and then create relationships between Table2 and Table4 using MonthNumber. Below is the DAX logic for Table4 and True Overtime measure:

Table4 = 
SUMMARIZECOLUMNS (
    Table3[Month Number],
    TREATAS ( { 0 }, Table3[isHoliday] ),
    "Capacity", SUM ( Table3[Capacity] )
)

Total Capacity = 
CALCULATE (
    SUM ( Table4[Capacity] ),
    CROSSFILTER ( Table2[Month Number], Table4[Month Number], BOTH )
)

Total Spent Time = SUM(Table2[Spent Time])

True Overtime = 
SUMX (
    SUMMARIZE ( Table2, Table1[Name], Table2[Month Number] ),
    VAR OverTime = [Total Spent Time] - [Total Capacity]
    RETURN
        IF ( OverTime < 0, 0, OverTime )
)

[Ashish_Mathur]

Hi,

From your pasted picture, i cannot understand which are the 3 Tables.  Paste them clearly again.

[Vitaliy]

Table1: Name (1 field)
Table2: Date, Month_number, Spent Time, Name (4 fields)
Table3: Month_number, Capacity, isHolidays (3 fields)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI solution file from here.

Hope this helps.

[Vitaliy]

Thanks a lot, this is really great solution!
@AkhilAshok, thanks, your solution is nice too!

[Ashish_Mathur]

You are welcome.