Forum Discussion

Fraze's avatar
Fraze
Helper I
3 years ago

Running Total (Editor/CalculatedColumn/Measure?) and Average Running Total (Measure?)

Hello,

 

My current issue is 2-fold. I have figured out how to solve each independently, but none of the approaches I have found solve both simultaneously. Test file attached here

 

1 - Running Total of NetChange

  • Needs to be filterable for any Date or CustNo; however, if for example June is selected, the Running Total needs to still include all previous data rather than starting again at $0 on June 1.
  • My current solution is a measure, but it creates issues for my next challenge so I feel a calculated column might be required

 

2 - Dynamic Average of Running Total

  • Need the Average Running Total for the date range selected, once again filterable by Date and CustNo. If june is selected, I need the average of 30 days worth of Running Total.
  • Note, this is the Running Total average not the average of NetChange. So if there is only one June transaction on the 1st for $30,000, the Running Total Average for the month would be $30,000, NOT (30,000 + 0 + 0 + ...)/30    

 

Thanks!

General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Fraze ,

     

    Do you want to calculate the average of running total by logic as running total divide days whose netchange not equal to 0?

    Here I suggest you to try this code.

    Avg = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Transactions&Date',
        [Date],
        "NetChange", CALCULATE ( SUM ( 'Transactions&Date'[NetChange] ) )
    )
VAR _COUNT_DAY =
    COUNTAX ( FILTER ( _SUMMARIZE, [NetChange] <> 0 ), [Date] )
RETURN
    DIVIDE ( [Running Total], _COUNT_DAY )

    In June there are 24 days whose Netchange <>0.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Fraze's avatar
      Fraze
      Helper I
      3 years ago

      Hello,

       

      No, I am looking for the basic average. So if we keep using June as the example, the average Running total is -53,271. So the average calculation formula is very basic, what is challenging is that it is doing it on the running total measure. Which is why I am also curious if the running total should be converted to a calcualted column.