Calculate a running total based on criteria

[SamFletch]

Hello all,

I'm relatively new to DAX and Powery Query and finally looking to dip my toes into a bit more advance features and I need some help.

I am running a simple report that looks at vehicle manufacturers, the age of the vehicle (in months) and the average cost of maintenance for that month. An example of the table I'm using is below:

Manufacturer	Age In Months	Average Job Cost
Ford	1	£100
Ford	2	£110
Ford	3	£100
Ford	4	£100
Mercedes	1	£80
Mercedes	2	£80
Mercedes	3	£60
Mercedes	4	£120
Fiat	1	£45
Fiat	2	£50
Fiat	3	£55
Fiat	4	£50

I have then created a measure within PowerBI to calculate a running total, which I've typed up as so:

Life Cost = 
CALCULATE(
    SUM(Sheet1[Average Job Cost]),
    FILTER(
        ALL('Sheet1'),
        'Sheet1'[Age in Months] <= MAX('Sheet1'[Age in Months])
    )
)

This gives me a running total for all costs:

Whilst this is handy, I really need to distinguish it further where I can see the running total not only for all manufacturers but for the individual ones as well. At present, when I add manufacturers to any of my visualisations, it they just show the same value for each rather than the different ones you would expect. 

I've added a column to the table to show more clearly what I mean.

Manufacturer	Age In Months	Average Job Cost	Life Cost
Ford	1	£100	£100
Ford	2	£110	£210
Ford	3	£100	£310
Ford	4	£100	£410
Mercedes	1	£80	£80
Mercedes	2	£80	£160
Mercedes	3	£60	£220
Mercedes	4	£120	£340
Fiat	1	£45	£45
Fiat	2	£50	£95
Fiat	3	£55	£150
Fiat	4	£50	£200

For what it's worth, Slicers do show the appropriate changes but I'm needing something that shows it all in one space rather than having to click through.

Hope I've made myself clear, thank you for the help.

[Ashish_Mathur]

Hi,

Is the average job cost a measure that you have written?  Share the link from where i can download your PBI file.

[amitchandak]

@SamFletch ,  The information you have provided is not making the problem clear to me. Can you please explain with an example.
Try measure like

Life Cost = 
CALCULATE(
    SUM(Sheet1[Average Job Cost]),
    FILTER(
        allselected('Sheet1'),
        'Sheet1'[Age in Months] <= MAX('Sheet1'[Age in Months])
    )
)

Appreciate your Kudos.

[littlemojopuppy]

@amitchandak I posted a response to this five hours ago with a screen shot showing that the solution I offered works.  Why did you feel that another solution was necessary???

[littlemojopuppy · Accepted solution]

Hi @SamFletch 

Try this instead

Life Cost :=
CALCULATE (
    SUM ( Sheet1[Average Job Cost] ),
    FILTER (
        ALLEXCEPT ( 'Sheet1', Sheet1[Manufacturer] ),
        'Sheet1'[Age in Months] <= MAX ( 'Sheet1'[Age in Months] )
    )
)

Hope this helps!  🙂

[SamFletch]

Thank you, this is exactly it!

I knew it would be something simple with the way I used "FILTER". 

Much appreciated for the quick and thorough response 🙂