Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

Cumulative Sum of 2 Rows

Hi guys,

 

 

 

Above is the formula I created in Excel. Row 4 is what I'm trying to create in Power BI, but I'm not sure how do I achieve that. 

Here is the Dax function I wrote but I'm not getting the correct value in November and December

 

Measure = CALCULATE([Total]),FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))

Can someone please help me?

 

Thank you

Need Help
Show and Tell
Tutorial Requests
  • mwegener's avatar
    mwegener
    7 years ago

    Hi Stuznet

     

    Row3 = 
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum

9 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    7 years ago

    Hi Stuznet,

     

    maybe this helps.

     

    Measure = IF(HASONEFILTER(Table1[Row]);SUM(Table1[Value]);CALCULATE(SUM(Table1[Value]);FILTER(ALLSELECTED(Table1);Table1[Month Num] <= Max(Table1[Month Num]))))

     

    Best regards,

     

    Marcus

    • Stuznet's avatar
      Stuznet
      Helper V
      7 years ago

      @mwegeber,
      Thank you for providing your solution, that’s the correct value I’m looking for but I forgot to mention that in power bi, I created row 2 and 3 with a separate measures

      Row 2 =
VAR abc = CALCULATE( [total] / 1000000
RETURN
IF(MAX( Table [Month Num] + 1 >= MONTH(TODAY()), abc , BLANK())



      Row3 =
VAR def = CALCULATE( [total], Filter( Table, Table[ Date] <> BLANK())) / (1000000)
RETURN
IF(MAX( Table [Month Num] + 2 >= MONTH(TODAY()), def , BLANK()


      When I plugged in the measure in your solution I’m not getting the result

      Measure = IF(HASONEFILTER( Table[Row]), CALCULATE(
[Row2] + [Row3] , FILTER(
ALLSELECTED( Table) , Table[Month Num] <= MAX(Table[Month Num]))))



      Could you please advise? Thank you

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        7 years ago

        Hi Stuznet

         

         

        Measure = CALCULATE([Row2]+[Row3],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    What is actually there in the first column.  Are they years by any chance?  Can you share your PBI file with dummy figures?

    • Stuznet's avatar
      Stuznet
      Helper V
      7 years ago

      Ashish_Mathur

       

      Here is the dummy report Cumulative Sum of 2 Rows, it is exactly what I'm trying to achieve on my active report.

       

      Row 3 accumulating from Row 2. IF November and December <> BLANK(), I want it to accumulate from Row1 starting from October to December. 

       

      Note: Row 3 November to December number is the wrong result. 

       

      Thank you so you so much for looking into this.

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        7 years ago

        Hi Stuznet

         

        Row3 = 
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum