Running Total - looking for a more elegant solution

[ns29]

I'm looking for a more elegant solution to set up a Total Run on the selected items. See the attached screenshot and sample file at the following link. In the sample file, on the running_total tab, you will see 2 arrays. The matrix on the right is what I ultimately need, the one on the left is to provide clarity on what I'm trying to achieve. For the measurement performance_running_total_selected,I basically want to start the total career from the age of 2. Is there a better or elegant way to do this than the hack in the highlighted part of the screenshot? Is it a different solution if the desired range over which the total execution should be made is continuous (between X and Y) versus the exclusion of specific selected ages?

https://drive.google.com/file/d/1ssR6Y40tEBuPARHfrVzDm5QJumz3g4y5/view?usp=sharing

[v-gizhi-msft]

Hi,

Please try to create a What If parameter:

Parameter = GENERATESERIES(1, 24, 1)

Then try this measure:

performance_running_total_selected = 
CALCULATE(
	SUM('Table1'[performance]),
	FILTER(
		ALL('Table1'[age]),
		ISONORAFTER(IF([age]<=SELECTEDVALUE(Parameter[Parameter])-1,1000,[age]), MAX([age]), DESC)
	)
)

If you want to start from 2, when select '2' in parameter slicer, the result shows:

Here is the changed pbix file:

pbix 

Best Regards,

Giotto Zhi

[ns29]

Thanks for this - learning new things about Power BI every day!!!

[danextian · Accepted solution]

Hi @ns29 ,

Try this:

RunningTotal = 
CALCULATE (
    SUM ( Table1[performance] ),
    FILTER (
        FILTER ( ALL ( Table1[age] ), Table1[age] >= 2 ),
        Table1[age] <= MAX ( Table1[age] )
    )
)

[ns29]

Thank you for the elegant solution!