Running Total in Table Visual

[martindoll]

I have a simple problem that I can't figure out.

The table below....the RT column is a simple running total that resets on change of vendor no.  The list will alway be sorted by Vendor No, and I need it is a table for the Export to Excel ....matrix won't export properly.  I ahve tried every which way....can someone please give me the DAX to make this happen...and is it a Calc Column or a Measure?

Thanks

Vendor No	Invoice Amount	RT
1001	100	100
1002	100	100
1002	100	100
1003	100	100
1003	100	200
1003	100	300
1004	100	100
1004	100	100

[PaulDBrown]

@martindoll 

Apologies upfront, but even though this can be calculated using DAX, if the final destination is Excel, why not do it in Excel itself by exporting the table as you have it and doing it there?

[martindoll]

Well , ok, but the idea is for the user not not have to mess with the export.....it is meant to be dropped into review sheet.  That is what she is doing now, exporting data and creating formulas....

[edhans]

@martindoll - you need some other field in this table to work. Power BI doesn't understand rows like Excel does where you can reference rows above/below. An Invoice Number for example.

I added an Index to your data so it looks like below. Just go to the Add Columns ribbon in Power Query, then add an Index, but ideally you have some other mechanism (date, invoice number, etc) to do this with.

Then the following measure works:

Running Total =
VAR varCurrentIndex =
    MAX( Data[Index] )
RETURN
    CALCULATE(
        SUM( Data[Invoice Amount] ),
        Data[Index] <= varCurrentIndex,
        REMOVEFILTERS(
            Data[Index],
            Data[Invoice Amount]
        )
    )

It returns this:

I agree with @PaulDBrown on using Power BI here. If the goal is to get it into Excel, then do it in Excel. You can use Power Query there the same way and load this into the Power Pivot data model, add your measure there, and then the user simply refreshes their report.

[martindoll]

Ok, great yes, I understand about Excel....I was trying to keep the process within Business Central...pin a PBI report on the Home Page.....but I'll go down that path....I had just talked to the end user about doing it within Excel.

[edhans]

Great @martindoll - right tool for the job. I prefer Power BI 99% of the time when DAX and Power Query are involved, but sometimes, Excel is the right place.

Other alternative is do it in Power BI then connect to Power BI from Excel using the Get Data/From Power BI menu option, which requires the user has a Power BI Pro license and have a pretty recent version of Excel. July 2020 or later I think...

Please mark one of these answers as the solution if we've helped so this thread can be marked as solved.