Rolling average of increasing value

[joggis]

Hi all,

I am now on the Power BI front and I am looking for some tips on how to do the following:

My data set:

Car	N-Cars	Miles-days
BMW	1	33
BMW	2	44
FORD	1	10
FORD	2	40
FORD	3	20
FORD	4	33

I want to calculate the following:

Car	N-Cars	Miles-days	average miles-day / N-Cars
BMW	1	33	33
BMW	2	44	38.5
FORD	1	10	10
FORD	2	40	25
FORD	3	20	23.33333333
FORD	4	33	25.75

Then plot this in a line diagram with Miles- days on Y-Axis and averaged value on X-axis.

For BMW it would be (1,33) next point (2,38.5).

Is there anybody here that could help with this?

[v-juanli-msft]

Hi @joggis 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[amitchandak]

Try using the quick measure.

[sturlaws · Accepted solution]

hi @joggis ,

you can create a calculated column like this:

average miles-day / N-Cars =
VAR _currentNCar =
    CALCULATE ( SELECTEDVALUE ( cars[N-Cars] ) )
VAR _currentCar =
    CALCULATE ( SELECTEDVALUE ( cars[Car] ) )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( cars[Miles-days] );
            FILTER ( ALL ( cars ); cars[Car] = _currentCar && cars[N-Cars] <= _currentNCar )
        );
        _currentNCar
    )

here is example I made using the data you have provided: pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.