cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ravin54 Frequent Visitor
Frequent Visitor

Running Total Through Direct Query

I need to calculate the running total for a tabular data. I need to use direct query mode.

There are Filters(Slicers) for Date and Product in the report. When we filter the data based on Date and Product, the running total should be calculated as per the filtered applied.

 

DateProductCostRunning Total
1/1/2019A1010
1/1/2019B2030
1/1/2019C2555
1/2/2019A45100
1/2/2019C65165
1/3/2019B15180
1/3/2019A20200

 

Need help in achieving data like above.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Running Total Through Direct Query

@Ravin54 ,

 

You may refer to the measure below.

Measure =
CALCULATE (
    SUM ( 'Table1'[Cost] ),
    FILTER (
        SUMMARIZE ( ALLSELECTED ( 'Table1' ), 'Table1'[Date], 'Table1'[Product] ),
        ISONORAFTER (
                'Table1'[Date], MAX ( 'Table1'[Date] ), DESC,
                'Table1'[Product], MAX ( 'Table1'[Product] ), DESC
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Running Total Through Direct Query

@Ravin54 ,

 

You may refer to the measure below.

Measure =
CALCULATE (
    SUM ( 'Table1'[Cost] ),
    FILTER (
        SUMMARIZE ( ALLSELECTED ( 'Table1' ), 'Table1'[Date], 'Table1'[Product] ),
        ISONORAFTER (
                'Table1'[Date], MAX ( 'Table1'[Date] ), DESC,
                'Table1'[Product], MAX ( 'Table1'[Product] ), DESC
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ravin54 Frequent Visitor
Frequent Visitor

Re: Running Total Through Direct Query

Hi @v-chuncz-msft ,

 

Thank you for your help. Smiley Happy

 

I was able to achieve the required runnning total using the Measure formulae you shared.

 

Thanks,

Ravin