Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

New calculated column using row context to select value from same table

  I have a table showing yearly Sales and Profit values at specific years for two different companies:   Another table specifies reference years for each company.         I would ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try with this column

     

    Value at Ref Year Column =
CALCULATE (
    SUM ( Sales_and_Profit[Value] ),
    FILTER (
        ALLEXCEPT (
            Sales_and_Profit,
            Sales_and_Profit[Company],
            Sales_and_Profit[Attribute]
        ),
        Sales_and_Profit[Year] = RELATED ( RefYear[Year] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

Try with this column

 

Value at Ref Year Column =
CALCULATE (
    SUM ( Sales_and_Profit[Value] ),
    FILTER (
        ALLEXCEPT (
            Sales_and_Profit,
            Sales_and_Profit[Company],
            Sales_and_Profit[Attribute]
        ),
        Sales_and_Profit[Year] = RELATED ( RefYear[Year] )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Zubair,

     

    Thanks, it worked perfectly and also helped me understand a bit more about the combined use of the CALCULATE and FILTER functions.

     

    Cheers

     

    Karim