Running total on avg measure from the direct query

[Laiq_Rahman]

Hi,

I'm connecting to a big table using direct query. I've to create a average measure based on a table which is working perfect.

BID_VOL = 
var avg_bid_avail = CALCULATE(AVERAGE(dataSAMPLE[BID_AVAIL]), FILTER(dataSAMPLE, dataSAMPLE[BID_VOLUME] <> 0))
var AVG_BID_VOLUME = CALCULATE(AVERAGE(dataSAMPLE[BID_VOLUME]), FILTER(dataSAMPLE, dataSAMPLE[BID_VOLUME] <>0))
return IF(MAX(DATAAVAILABLE[dataavailable]) = "ALL", AVG_BID_VOLUME,         avg_bid_avail)

Then i've to create running total based on the outcome of this data. I created a measure for it.

BID_VOL running total in STATION_NAME = 
CALCULATE(
	[BID_VOL],
	FILTER(
		ALLSELECTED(dataSAMPLE[STATION_NAME]),
		ISONORAFTER(dataSAMPLE[STATION_NAME], MAX(dataSAMPLE[STATION_NAME]), DESC)
	)
)

I am getting the below output but this is not what i am expecting.

I want an output like below.

I've added a sample pbix file for your review. Can you please help what would be the dax for it?  

https://drive.google.com/file/d/1xYA-r3hxRrydQ_a_qc16NNnQWt_dpi-k/view?usp=sharing

Please note it's a direct query and connecting to big table. So, adding calculated column or adding index column in power query mayn't help.

Regards,

Laiq

[MFelix · Accepted solution]

Hi @Laiq_Rahman 

Try the following code:

BID_VOL running total in STATION_NAME = SUMX(
		SUMMARIZE(
			FILTER(
				ALLSELECTED('dataSAMPLE'),
				'dataSAMPLE'[STATION_NAME] <= MAX('dataSAMPLE'[STATION_NAME])
			),
			'dataSAMPLE'[STATION_NAME],
			"D", [BID_VOL]
		),
		[D]
	)

File attached.

[Laiq_Rahman]

Awesome. I've spent days to figure out. Problem solved now.... Thank you so much.