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] )
)