Forum Discussion
Aggregated Calculations
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!
6 Replies
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 ) )Hi,
From your pasted picture, i cannot understand which are the 3 Tables. Paste them clearly again.
- Table1: Name (1 field)
Table2: Date, Month_number, Spent Time, Name (4 fields)
Table3: Month_number, Capacity, isHolidays (3 fields)
