Forum Discussion
Impactful IndividualRunning total, Mono poly style
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.
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.
Impactful IndividualWhy 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 :).