Forum Discussion
Impactful IndividualRunning total, Mono poly style
So, I have the problem that if you start of with, say, 200 and then per turn have the option to earn some or lose some, randomly at turn, for example, 50 or 67, you may hit rock bottom, lost all your money and have to start over. After you have hit rock bottom, the counting start again from 200.
If the only thing you have is your start position and the lost or earned values per turn, how can you define how much money one has in turn X?
| Turn | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Capital | 200 | 180 | 130 | 190 | 0 | 200 | 0 | 200 | 230 |
| Earn/ Loss | 0 | -20 | -50 | 60 | -250 | 0 | -250 | 0 | 30 |
Like, till turn 5, you can just sum all earned and losses as a running total and add 200 starting capital to it. But at turn 6, that no longer works, neither at 7. In my brain, I know how to solve this. In Excel, I know how to solve this. Power BI on the other hand... not so much.
How would you make it at turn 8 state you're starting a new run when all you have is this:
Start: 200
| Turn | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Earn/ Loss | 0 | -20 | -50 | 60 | -250 | 0 | -250 | 0 | 30 |
?
EDIT:
It be like this in Excel:
Hi DouweMeer
Thanks for the reply from Sahir_Maharaj and Greg_Deckler , please allow me to provide another insight:
Please try this measure:
MEASURE = VAR _currentTurn = MAX ( 'Table'[Turn] ) VAR _vtable = ADDCOLUMNS ( SELECTCOLUMNS ( ALLSELECTED ( 'Table' ), 'Table'[Earn/ Loss], "_Turn", 'Table'[Turn] ), "_CurrentBN", ROUND ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Turn] <= EARLIER ( [_Turn] ) ), 'Table'[Earn/ Loss] ) / 200, 0 ) ) VAR _vtable2 = ADDCOLUMNS ( _vtable, "_previoudN", IF ( MAXX ( FILTER ( _vtable, [_Turn] = EARLIER ( [_Turn] ) - 1 ), [_CurrentBN] ) > [_CurrentBN], 1 ) ) VAR _vtable3 = ADDCOLUMNS ( _vtable2, "Sort", RANKX ( FILTER ( _vtable2, [_previoudN] <> BLANK () ), [_Turn],, ASC ) ) RETURN MAXX ( FILTER ( ADDCOLUMNS ( _vtable3, "_outcome", IF ( 200 + SUMX ( FILTER ( _vtable3, [Sort] = EARLIER ( [Sort] ) && [_Turn] <= EARLIER ( [_Turn] ) ), [Earn/ Loss] ) < 0, 0, 200 + SUMX ( FILTER ( _vtable3, [Sort] = EARLIER ( [Sort] ) && [_Turn] <= EARLIER ( [_Turn] ) ), [Earn/ Loss] ) ) ), _currentTurn = [_Turn] ), [_outcome] )The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Greg_Deckler
Community Champion
DouweMeer The Earn/Loss being 0 indicates the start of a new run.
DouweMeerNo, like with the game, you can also just have a turn without loss or profit. By itself it is insufficient.
- Sahir_Maharaj
Super User
Hello DouweMeer,
Can you please try the following:
Capital = VAR StartCapital = 200 VAR RunningTotal = CALCULATE( SUM(TurnData[Earn_Loss]), FILTER( ALL(TurnData), TurnData[Turn] <= MAX(TurnData[Turn]) ) ) VAR ResetCheck = SUMX( FILTER( ALL(TurnData), TurnData[Turn] <= MAX(TurnData[Turn]) ), [Earn_Loss] ) RETURN IF( RunningTotal + StartCapital < 0, StartCapital, RunningTotal + StartCapital )- DouweMeer
I'm not seeing within your measure at turn 8 the possibility for a third time a new starting capital. Wouldn't your measure do this?
Turn 1 2 3 4 5 6 7 8 9 Earn/ Loss 0 -20 -50 60 -250 0 -250 0 30 Running Total 0 -20 -70 -10 -260 -260 -510 -510 -480 Measure 200 180 130 190 200 200 200 200 200 Like, besides at 5 it should turn 0 instead of 200, but the running total just keeps counting after reset.
Hi DouweMeer
Thanks for the reply from Sahir_Maharaj and Greg_Deckler , please allow me to provide another insight:
Please try this measure:
MEASURE = VAR _currentTurn = MAX ( 'Table'[Turn] ) VAR _vtable = ADDCOLUMNS ( SELECTCOLUMNS ( ALLSELECTED ( 'Table' ), 'Table'[Earn/ Loss], "_Turn", 'Table'[Turn] ), "_CurrentBN", ROUND ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Turn] <= EARLIER ( [_Turn] ) ), 'Table'[Earn/ Loss] ) / 200, 0 ) ) VAR _vtable2 = ADDCOLUMNS ( _vtable, "_previoudN", IF ( MAXX ( FILTER ( _vtable, [_Turn] = EARLIER ( [_Turn] ) - 1 ), [_CurrentBN] ) > [_CurrentBN], 1 ) ) VAR _vtable3 = ADDCOLUMNS ( _vtable2, "Sort", RANKX ( FILTER ( _vtable2, [_previoudN] <> BLANK () ), [_Turn],, ASC ) ) RETURN MAXX ( FILTER ( ADDCOLUMNS ( _vtable3, "_outcome", IF ( 200 + SUMX ( FILTER ( _vtable3, [Sort] = EARLIER ( [Sort] ) && [_Turn] <= EARLIER ( [_Turn] ) ), [Earn/ Loss] ) < 0, 0, 200 + SUMX ( FILTER ( _vtable3, [Sort] = EARLIER ( [Sort] ) && [_Turn] <= EARLIER ( [_Turn] ) ), [Earn/ Loss] ) ) ), _currentTurn = [_Turn] ), [_outcome] )The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- DouweMeer
Why does everyone always try to solve it with a measure and never calculated columns 🙂 ?
Haven't tested it yet, but by what you did seems pretty much what I had in mind. You're creating a dynamic table of the full range at every turn to get to the value, pretty much what Excel does. "EARLIER" is quite handy with this, wasn't aware of that expression.
It does answer my raised question, but not really my challenge. I did solve it however myself by creating a custom table with per custom column a turn referring back to the prior. Luckily there's a fixed maximum amount of turns within the challenge that's doable manually and can be less.
The main challenge I have is two folds. One is that "the game" isn't ran once, but like a competition like a 100 times. Each is unique and can't be interchanged, which is why I dislike the measure solution. For the intended purpose, it's not allowed to change by context and should be able to be summarized on turns between the games. Another reason I dislike the measure solution. The other problem, the threshold of <0 varies each "turn", though I can easily see that referred back in your last sumx expressions within the maxx.
Anyway, the principle you proposed gives me some things to think about. It's a better scaling method than my manual custom columns. Thank you :).
