Forum Discussion

Namoh's avatar
Namoh
Post Partisan
6 years ago
Solved

Calculated column using data from multiple rows and columns

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

 

DateUnit Hrs planned downHrs unplanned downno breakdownshours in monthCalculated column
1/1/2019Unit 1 8204744158,54
1/1/2019Unit 2 16246744122,24
1/2/2019Unit 1 5124672158,54
1/3/2019Unit 2 382744122,24
1/3/2019Unit 1 24305744158,54
1/2/2019Unit 2 13189672122,24
1/2/2019Unit 3 684672193,43
1/4/2019Unit 3 9153720193,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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • Namoh's avatar
    Namoh
    Post Partisan
    6 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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