Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

How to Sum two values into one Row

Hi guys,   I'm struggling to sum two measures into one Row. I have two Measure Measure 1   Var = CALCULATE(SUM([Value]), FILTER( ALLSELECTED(Actual), Actual[Mont...
  • Anonymous's avatar
    Anonymous
    7 years ago

    Try this measure

    Result = 
    VAR LastMonthInActuals =
        MAX ( Actual[Month_Num] )
    VAR MonthInContext =
        MAX ( Month_Tbl[Month_Num] )
    VAR CumulativeTotalOfActual =
        SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
    VAR CumulativeMonthlyActuals =
        CALCULATE (
            SUM ( [Value] ),
            FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals )
        )
    VAR CumulativeForecast =
        SUMX (
            FILTER (
                ALL ( Forecast ),
                Forecast[Month_Num] > [LastMonthInActuals]
                    && Forecast[Month_Num] <= MonthInContext
            ),
            Forecast[Value]
        )
    RETURN
        IF (
            MonthInContext <= LastMonthInActuals,
            CumulativeMonthlyActuals,
            CumulativeForecast + CumulativeTotalOfActual
        )
  • Stuznet's avatar
    Stuznet
    7 years ago

    Anonymous 

    Thank you so much for the measure, the result was wrong but I modified it and it works :)

    See measure in Red. Your original measure was Forecast[Value], I replaced with Forecast[Forecast_Var])

     

    Forecast_Var = CALCULATE(SUM(Forecast[Value]),
                    FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))

     

    This is the final measure

     

    Measure = 
    VAR LastMonthInActuals = MAX ( Actual[Month_Num] )
    VAR MonthInContext = MAX ( Month_Tbl[Month_Num] )
    VAR CumulativeTotalOfActual = SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
    VAR CumulativeMonthlyActuals = CALCULATE (SUM ( Actual[Value] ),FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals ))
    VAR CumulativeForecast = SUMX (FILTER (ALL ( Forecast ),Forecast[Month_Num] > LastMonthInActuals && Forecast[Month_Num] <= MonthInContext), Forecast[Forecast_Var])
    RETURN
        IF (
            MonthInContext <= LastMonthInActuals,
            CumulativeMonthlyActuals,
            CumulativeForecast + CumulativeTotalOfActual)

     

    This the result and it summing correctly.

     

     

    Thank you so much y'all. :)