Forum Discussion

fourmj's avatar
fourmj
Frequent Visitor
4 years ago
Solved

Conditionally Sum or Average a Column based on dimension table values

Hello, I have a fact table that contains Forecast data for particular business divisions. The forecast works as such: On the last day of the quarter, there are 9 future quarters that provide forecast...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi fourmj 
    Please try

    MyMeasure =
IF (
    MAX ( '1_model_calc'[Calculation Type] ) = "Average",
    SUMX (
        VALUES ( 'Forecast'[Sub-Division2] ),
        CALCULATE ( AVERAGE ( 'Forecast'[Values] ) )
    ),
    SUM ( 'Forecast'[Values] )
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi fourmj 
Please try

MyMeasure =
IF (
    MAX ( '1_model_calc'[Calculation Type] ) = "Average",
    SUMX (
        VALUES ( 'Forecast'[Sub-Division2] ),
        CALCULATE ( AVERAGE ( 'Forecast'[Values] ) )
    ),
    SUM ( 'Forecast'[Values] )
)
fourmj's avatar
fourmj
Frequent Visitor
4 years ago

Thank you, this measure works. I also tried it on a different set of data that is similar to the sample data in this post and it worked with some modification.

 

Thank you for providing me with insight on solving this problem.