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[Month_Num] <= MAX(Actual[Month_Num])))

 

 

Measure 2

 

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

 

 

I did it in Excel and this is the result I wanted to appear in power bi

 

May + June = 

4228 + 315,489 = 319,717

 

June + July

319,717 + 132,467,489 = 132,782,978

and so on ......

 

Here is the dummy file

 

I really appreciate your help

Need Help
Show and Tell
Tutorial Requests
  • 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. :)

     

11 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Stuznet 

    This will need to create a new table with some measures and columns.

    Where do you apply filters?

    Which is static and which is dynamic?

    Because my solution may be static for some part.

     

    Best Regards
    Maggie

     

     

    • Stuznet's avatar
      Stuznet
      Helper V
      7 years ago

      Hi v-juanli-msft 

      I'm unsure what you mean but this is the third measure I created but it doesn't sum May + June, June + July. 

      Total = SWITCH(TRUE(),
            MAX(Category[Category]) = "Cat1",
                [Forecast_Var] + [Var])

      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        It took me a bit, but I think what you want is a cumulative sum? Your first measure is a cumulative sum, but then you get weird in the forecasting bit. 

         

        If you could explain how you calculate the Forecast[Value] in the first place, it would be very helpful. In your expected outcome, it looks like Measure2 is already set up as a reverse cumulative sum (essentially summing everything that hasn't happened yet), and then you're looking to add the previous month's reverse sum again in the table?  

         

        If this is truly the case, you may be able to use this measure to get the forecasted month values:

        Forecast_Var_plus_prev_month = CALCULATE(SUM(Forecast[Value]),
                FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())-1))

        Either way, the math that I'm seeing seems weird, and I'd love to know what's up.