DAX Running Total - based on the visual sort

[Anonymous]

I've found the easy way to calculate a running total , (by day), is this

Running Total Sales =
CALCULATE (
[Sales Amount]
FILTER ( ALL ( 'Date'), 'Date'[Date] <= MAX ( 'Date'[Date ) )
)

eg ;

Date : Running Total Sales
Jan-1 : $100
Jan-2 : $150

But, if you have more than 1 sale in a day, and you want to show the order number, i think the context transition causes power bi to filter on that order number, and my running total measure turns into a line total.

Date : Order : Running Total Sales
Jan-1 : #1 : $60
Jan-1 : #3 : $40
Jan-2 : #2 : $20
Jan-2 : #4 : $30

I haven't figured out how to remove the Order number filter from my measure (happy for any advice there) , but my problem goes one step further.

If , in the table, the user decides they want to sort on Order Number ( maybe they want to see what inventory we had promised to sell based on when we got the order ) I need the running total to calculate the amount based on all the lines above it (following the rules sort order).

eg ; sorted by Order

Date : Order : Running Total
Jan-1 : #1 : $60
Jan-2 : #2 : $80
Jan-1 : #3 : $120
Jan-2 : #4 : $150

Can I detect what the sort order is, in the measure, and then create some sort of Summarize table in that order, and calculate the running total ??

I haven't had any luck googling this one , sorry if it's already been explained.

( funnily enough, this is the simplest thing in the world to doi in excel ...   "total = total cell in line above plus line-amount" )

cheers,
Jarrod

[Barthel]

Hey,

As far as I know there is no way to find out in DAX how a table is sorted. An alternative is to add a slicer in which you indicate whether you want to see your order ascending or descending. Create a table ('Order') with one column ('Order') consisting of the values ​​'Desc' and 'Asc'. Use this table as a slicer in your report. You can use the following DAX code for your Running Total:

Running Total =
VAR DescOrAsc =
    SELECTEDVALUE ( Order[Order] )
VAR CurrentOrder =
    SELECTEDVALUE ( Table[Order] )
RETURN
    IF.EAGER (
        DescOrAsc = "Asc",
        CALCULATE ( [Sales Amount], Table[Order] <= CurrentOrder, ALLSELECTED () ),
        CALCULATE ( [Sales Amount], Table[Order] >= CurrentOrder, ALLSELECTED () )
    )