Forum Discussion
Count Consecutive Increasing Amounts
I have the following table. I need a calculated column that returns the count of consecutive LotNumber that has an increasing Amount. I have browsed similar solution but I cant get one to work on my table without crashing the pbix file
| Item | LotNumber | Amount | IncreasingConsecutiveCount |
| Table | 1 | 10 | 1 |
| Table | 3 | 20 | 2 |
| Table | 5 | 30 | 3 |
| Table | 9 | 20 | 1 |
| Table | 11 | 10 | 1 |
| Table | 13.6 | 20 | 2 |
| Table | 16.2 | 90 | 3 |
| Table | 18.8 | 10 | 1 |
| Table | 21.4 | 11 | 2 |
| Table | 24 | 12 | 3 |
| Table | 25 | 13 | 4 |
| Table | 26 | 14 | 5 |
You can try the following modified calculated columns
Rank = RANKX ( FILTER ( 'Table', [Item] = EARLIER ( 'Table'[Item] ) ), [LotNumber], , ASC )Flag = VAR a = CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Item] ), 'Table'[Rank] = EARLIER ( 'Table'[Rank] ) - 1 ) RETURN IF ( [Amount] > a, 1, 0 )ConsecutiveCount = VAR a = MAXX ( FILTER ( 'Table', [Flag] = 0 && [Item] = EARLIER ( 'Table'[Item] ) && [Rank] <= EARLIER ( 'Table'[Rank] ) ), [Rank] ) RETURN SWITCH ( TRUE (), a = BLANK (), [Rank], [Rank] = a, 1, [Rank] - a + 1 )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Thank you for that. Would you know why when you add another column everything turns to 1?
Hi,
Thanks for the solution ThxAlot provided, it is excellent, and i want to offer some more information for user to refer to.
hello PowerBITestingG , based on your descriotion, you can create the following calcualted columns.
Rank = RANKX('Table',[LotNumber],,ASC)Flag = VAR a = LOOKUPVALUE ( 'Table'[Amount], [Rank], [Rank] - 1 ) RETURN IF ( [Amount] > a, 1, 0 )ConsecutiveCount = VAR a = MAXX ( FILTER ( 'Table', [Flag] = 0 && [Rank] <= EARLIER ( 'Table'[Rank] ) ), [Rank] ) RETURN SWITCH ( TRUE (), a = BLANK (), [Rank], [Rank] = a, 1, [Rank] - a + 1 )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
