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.   Date Unit   Hrs planned down Hrs unplanned down no breakdowns hours in month Calculated column 1/1/...
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

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