HASONEVALUE

Running Total/Cumulative Total in DAX

 Say we have below data:

Now if we want to calculate the running total, something like below

Through DAX, we can write below measure:

Cumulative Amt =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

    FILTER

    (all(table_orders),

    table_orders[OrderDate] <= MAX(table_orders[OrderDate]

    )

    )

    )

But there is a catch here.

Say we have HotelName as slicer then cumulative total would not be just for that hotel.

We can modify the DAX measure as below:

Cumulative Amt slicer filter =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

     FILTER(ALLSELECTED(table_orders), table_orders[OrderDate] <= MAX(table_orders[OrderDate]))

)

Ideally if some HotelName filter is applied then we need cumulative total for just the hotel name.

SQL query:

select OrderID ,
OrderDate,	
HotelName,
FoodItem,
Bill_amt,
sum(Bill_amt) over (order by OrderDate) 
from Orders

selectedvalue

union

ALLNOBLANKROWS

RelatedTable function

FILTER function - count of orders whose bill amount is greater than avg bill amount