Calculated column using data from multiple rows and columns

[Namoh]

Hi, I want to add a (calculated) column based on values in different rows/columns.

Date	Unit		Hrs planned down	Hrs unplanned down	no breakdowns	hours in month	Calculated column
1/1/2019	Unit 1		8	20	4	744	158,54
1/1/2019	Unit 2		16	24	6	744	122,24
1/2/2019	Unit 1		5	12	4	672	158,54
1/3/2019	Unit 2		3	8	2	744	122,24
1/3/2019	Unit 1		24	30	5	744	158,54
1/2/2019	Unit 2		13	18	9	672	122,24
1/2/2019	Unit 3		6	8	4	672	193,43
1/4/2019	Unit 3		9	15	3	720	193,43

Don't know if this can be done with 1 calculated column or that I need multiple but I want to do the following:

A) add all hours from column "hours per month" where column Unit is the same with the same year in column Date, in this case that would be 744/672/744 for Unit 1, 744/744/672 for Unit 2, etc

B) add all hours from column "hours planned down" with the same rule as above, so 8/5/24 for Unit 1, 16/3/13 for Unit 2, etc

C) add all hours from column "hours unplanned down" with the same rule as above, so 20/12/30 for Unit 1, etc

D) add all hours from column "# breakdowns" with the same rule as above, so 4/4/5 for Unit 1, etc

Result calculated column should be (A-B-C)/D, which would be in this case: ((744+672+744)-(8+5+24)-(20+12+30))/(4+4+5)=158,54 for Unit 1.

Any help would be appreciatted.

[Anonymous · Accepted solution]

HI @Namoh,

You can use following calculate column formula to achieve your requirement:

Column =
CALCULATE (
    DIVIDE (
        SUM ( 'Table'[hours in month] ) - SUM ( 'Table'[Hrs planned down] )
            - SUM ( 'Table'[Hrs unplanned down] ),
        SUM ( 'Table'[no breakdowns] ),
        -1
    ),
    FILTER (
        ALL ( 'Table' ),
        [Unit] = EARLIER ( 'Table'[Unit] )
            && YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
    )
)

Regards,

Xiaoxin Sheng

[Namoh]

Ok, so I got a bit further.

What I want is not possible in PQE, but only in DAX.

I always made formulas in PQE and never in DAX.
Anyone who know show this can be done in DAX?