Need a running sum from a average measure in direct query

[Laiq_Rahman]

Hi,

I am connecting to a large table using direct query.

Here is my raw data sample :

INTERVAL_DATE

STATION_NAME

DAY_TYPE

STATIONID

POP

PORTFOLIO

REGION_CODE

RRP

PERIOD_ID

INTERVAL_DATEID

FUEL

DUID

BID_AVAIL

1/04/2021 0:00

AWF

WD

ARWF

O

Ararat WF

VIC1

28.62

6

20210401

Wind

ARWF1

28.62

1/04/2021 0:00

AWF

WD

ARWF

O

Ararat WF

VIC1

28.73

7

20210401

Wind

ARWF1

28.73

2/04/2021 0:00

AWF

NWD

ARWF

O

Ararat WF

VIC1

-12.21

149

20210402

Wind

ARWF1

-12.21

2/04/2021 0:00

AWF

NWD

ARWF

O

Ararat WF

VIC1

-12.21

150

20210402

Wind

ARWF1

-12.21

2/04/2021 0:00

AWF

NWD

ARWF

O

Ararat WF

VIC1

-2.7

115

20210402

Wind

ARWF1

-2.7

2/04/2021 0:00

AWF

NWD

ARWF

O

Ararat WF

VIC1

-2.7

116

20210402

Wind

ARWF1

-2.7

1/04/2021 0:00

Brarat

WD

ARWF

O

Ararat WF

VIC1

30.64

271

20210401

Wind

ARWF1

30.64

1/04/2021 0:00

Brarat

WD

ARWF

O

Ararat WF

VIC1

30.64

272

20210401

Wind

ARWF1

30.64

2/04/2021 0:00

Brarat

NWD

ARWF

O

Ararat WF

VIC1

32.74

53

20210402

Wind

ARWF1

32.74

2/04/2021 0:00

Brarat

NWD

ARWF

O

Ararat WF

VIC1

32.74

54

20210402

Wind

ARWF1

32.74

2/04/2021 0:00

Brarat

NWD

ARWF

O

Ararat WF

VIC1

32.74

10

20210402

Wind

ARWF1

32.74

2/04/2021 0:00

Brarat

NWD

ARWF

O

Ararat WF

VIC1

32.74

11

20210402

Wind

ARWF1

32.74

First, i've created a average dax on this :

AVG_BID_AVAIL = CALCULATE(AVERAGE(data7_1[BID_AVAIL]), FILTER(data7_1,data7_1[BID_AVAIL] <> 0))

Second, i'm trying to create running total based on measure "AVG_BID_AVAIL"

running total = CALCULATE(
    [AVG_BID_AVAIL]
    ,FILTER(
                    ALLSELECTED('data7_1'),
                        data7_1[STATION_NAME] <= MAX(data7_1[STATION_NAME]))
    , data7_1[BID_AVAIL] > 0
)

But it didn't work as expected and end up below figures.

I need the output should be like below : 

Please let me know how can i make this work.

thanks.

[Anonymous · Accepted solution]

Hi @Laiq_Rahman 

Please try this:
First of all, add a index column in power query:

Then create a measure:

running total =
VAR _currentIndex =
    SUM ( 'data7_1'[Index] )
VAR _AVG_Table =
    ADDCOLUMNS (
        SUMMARIZE (
            ALL ( data7_1 ),
            'data7_1'[STATION_NAME],
            "_Index", SUM ( data7_1[Index] ),
            "_AVG_BID", [AVG_BID_AVAIL]
        ),
        "sum",
            SUMX (
                FILTER (
                    SUMMARIZE (
                        ALL ( data7_1 ),
                        'data7_1'[STATION_NAME],
                        "_Index", SUM ( data7_1[Index] ),
                        "_AVG_BID", [AVG_BID_AVAIL]
                    ),
                    [_Index] <= EARLIER ( [_Index] )
                ),
                [_AVG_BID]
            )
    )
RETURN
    MAXX ( FILTER ( _AVG_Table, [_Index] = _currentIndex ), [sum] )

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Laiq_Rahman]

nope.. 

[Laiq_Rahman]

Nope.. not working.. Getting below result

[vs_7]

Hi @Laiq_Rahman ,

try below measure

Running Total = SUMX(
    FILTER(
        ALL('data7_1'),
        'data7_1'[STATION_NAME] <= MAX('data7_1'[STATION_NAME]) &&
        'data7_1'[INTERVAL_DATE] <= MAX('data7_1'[INTERVAL_DATE])
    ),
    [AVG_BID_AVAIL]
)

[vs_7]

hi @Laiq_Rahman 

RunningTotal = 
VAR CurrentRowDate = MIN(data7_1[INTERVAL_DATE])
RETURN
CALCULATE(
    [AVG_BID_AVAIL],
    FILTER(
        ALLSELECTED(data7_1),
        data7_1[INTERVAL_DATE] <= CurrentRowDate    )
)