Forum Discussion

tranguyen's avatar
tranguyen
Frequent Visitor
2 years ago
Solved

Sum for next row based on condition

  Hi everyone. I am a fresher in Power BI. Please help to look at 2 pictures below. - Week wash is a column - Rank is a column that was created by week wash and month - Distinctcustomerbycategory...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi tranguyen ,

     

    Try below:

    _Value = MAX('Table'[Value]) 

Measure = var _v = [_Value]
var _pre = ADDCOLUMNS('Table',"pre",SUMX(FILTER(ALL('Table'),[Rank]=EARLIER('Table'[Rank])-1),[_Value]))
RETURN IF(_v<=2,"blank",IF(SUMX(_pre,[pre])<=2,_v+SUMX(_pre,[pre]),_v))

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tranguyen ,

 

I made simple samples and you can check the results below:

Measure = var _v = MAX('Table'[Value])
var _pre = ADDCOLUMNS('Table',"pre",SUMX(FILTER(ALL('Table'),[Rank]=EARLIER('Table'[Rank])-1),[Value]))
RETURN IF(_v<=2,"blank",IF(SUMX(_pre,[pre])<=2,_v+SUMX(_pre,[pre]),_v))

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • tranguyen's avatar
    tranguyen
    Frequent Visitor
    2 years ago

    Thanks for helping. But Value in this case is a measure, When I used max([Value]) It showed:
    - The MAX function only accepts a column reference as the argument number 1.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi tranguyen ,

       

      Try below:

      _Value = MAX('Table'[Value]) 

Measure = var _v = [_Value]
var _pre = ADDCOLUMNS('Table',"pre",SUMX(FILTER(ALL('Table'),[Rank]=EARLIER('Table'[Rank])-1),[_Value]))
RETURN IF(_v<=2,"blank",IF(SUMX(_pre,[pre])<=2,_v+SUMX(_pre,[pre]),_v))

       

      Hope it helps!

       

      Best regards,
      Community Support Team_ Scott Chang

       

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.