cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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
Highlighted
Community Support
Community Support

Re: Running Total Through Direct Query

@Anonymous ,

 

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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Running Total Through Direct Query

@Anonymous ,

 

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.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Running Total Through Direct Query

Hi @v-chuncz-msft ,

 

Thank you for your help. 🙂

 

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

 

Thanks,

Ravin

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (752)