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 is a measure. It will count the number of days of each week except Sunday.

 

With the first picture below. I want to have a condition, If Distinctcustomerbycategory <=2, It will always plus for the next row with Rank = 2. After the value of Distinctcustomerbycategory will return Blank. If It doesn't have any value <= 2. Keep staying all value

 

  

 

The result I want.
Week wash       Rank               Distinctcustomerbycategory
March 2               1                                       Blank
March 9               2                                          8 (2+6)

March 16             3                                          6 

March 23             4                                          6                                              

March 30             5                                          6

 

 

With the seconde picture below.  If Distinctcustomerbycategory <=2.  It will always plus for the next row with Rank = 1 of next month ( first week of May month). After plus the value of Distinctcustomerbycategory will return Blank. If It doesn't have any value <= 2. Keep stay all value.

 

 

Week wash       Rank               Distinctcustomerbycategory
April 6                  1                                          6
April  13               2                                          6

April 20                3                                          5 

April 27                4                                          5                                              

April 30                5                                        Blank
May 5                   1                                         6 (2+4)

Thanks for helping 

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.

     

     

6 Replies

    • tranguyen's avatar
      tranguyen
      Frequent Visitor
      2 years ago

      Do you have any way to solve this besides updating visual calculations or this is the only way to solve it?

    • tranguyen's avatar
      tranguyen
      Frequent Visitor
      2 years ago

      After updating a new Power BI. What should I do now to solve my problems?

  • 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.