Forum Discussion

ns29's avatar
ns29
Helper I
6 years ago
Solved

Running Total - looking for a more elegant solution

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?

Capture.PNG

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

Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    6 years ago

    Hi ns29 ,

    Try this:

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

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    Hi ns29 ,

    Try this:

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

     

    • ns29's avatar
      ns29
      Helper I
      6 years ago

      Thank you for the elegant solution!

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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's avatar
      ns29
      Helper I
      6 years ago

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