Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I need to create a column of running totals based on a previous column. However, the previous column contains multiple iterations of one value. I need to be able to add my next value to just one unique value (not all iterations of it). Please see the referenced Excel screenshot for a better explanation. Please notice that the values will need to keep "Project" and "Sprint" in mind as filter criteria.
Thank you!
Solved! Go to Solution.
Hi @JLambs20 ,
You can create a calculated column or measure as below to achieve it:
1. Calculated column
Column for Cumulative Points =
VAR _tab =
SUMMARIZE (
FILTER (
ALL ( 'AZUP - Apollo Main Data' ),
'AZUP - Apollo Main Data'[Project]
= EARLIER( 'AZUP - Apollo Main Data'[Project] )
&& 'AZUP - Apollo Main Data'[Sprint]
<= EARLIER ( 'AZUP - Apollo Main Data'[Sprint] )
),
'AZUP - Apollo Main Data'[Project],
'AZUP - Apollo Main Data'[Sprint],
"point", MAX ( 'AZUP - Apollo Main Data'[Points] )
)
RETURN
SUMX ( _tab, [point] )
2. Measure
Cumulative Points =
VAR _tab =
SUMMARIZE (
FILTER (
ALL ( 'AZUP - Apollo Main Data' ),
'AZUP - Apollo Main Data'[Project]
= SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Project] )
&& 'AZUP - Apollo Main Data'[Sprint]
<= SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Sprint] )
),
'AZUP - Apollo Main Data'[Project],
'AZUP - Apollo Main Data'[Sprint],
"point", MAX ( 'AZUP - Apollo Main Data'[Points] )
)
RETURN
SUMX ( _tab, [point] )
Best Regards
Hi @JLambs20 ,
You can create a calculated column or measure as below to achieve it:
1. Calculated column
Column for Cumulative Points =
VAR _tab =
SUMMARIZE (
FILTER (
ALL ( 'AZUP - Apollo Main Data' ),
'AZUP - Apollo Main Data'[Project]
= EARLIER( 'AZUP - Apollo Main Data'[Project] )
&& 'AZUP - Apollo Main Data'[Sprint]
<= EARLIER ( 'AZUP - Apollo Main Data'[Sprint] )
),
'AZUP - Apollo Main Data'[Project],
'AZUP - Apollo Main Data'[Sprint],
"point", MAX ( 'AZUP - Apollo Main Data'[Points] )
)
RETURN
SUMX ( _tab, [point] )
2. Measure
Cumulative Points =
VAR _tab =
SUMMARIZE (
FILTER (
ALL ( 'AZUP - Apollo Main Data' ),
'AZUP - Apollo Main Data'[Project]
= SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Project] )
&& 'AZUP - Apollo Main Data'[Sprint]
<= SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Sprint] )
),
'AZUP - Apollo Main Data'[Project],
'AZUP - Apollo Main Data'[Sprint],
"point", MAX ( 'AZUP - Apollo Main Data'[Points] )
)
RETURN
SUMX ( _tab, [point] )
Best Regards
Hi,
Please show the expected result in a simple Table.
@JLambs20 , Try a measure like
sumx(addcolumns(summarize(Table, Table[project], Table[sprint], Table[points]), "_1",calculate(sum(Table[points]),filter(allselected(Table), [sprint] <=max([sprint]) && [project] = max([project])))),[_1])
Unfortunately, I'm getting an error. What am I missing?
\
@JLambs20 , after the filter getting closed after [sprint] before <= max is at the wrong place. remove and correct
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |