Need a dax to visualize output measure in different buckets

[Laiq_Rahman]

Hi,

I'm connecting to big table through direct query. I've created a measure like below to calculate bid volume.

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)

I've created running total of bid vol measure above.

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

Now i've created few parameters for different buckets like below.

I am planning to create a bar chart which gives a comparison between two interval in Interval column. Something like below.

I can't use summarise table option as i've direct query and needs to refresh based on date parameters. It's not refreshing when i change the dates. Is there any dax can help me on this. Please guide.

I've created a sample pbix for further reference. 

https://drive.google.com/file/d/1O7tZQFtUjH294Nqkkm-zNfGKCSnMlT5c/view?usp=drive_link

[Anonymous]

Hi @Laiq_Rahman ,

It seems that I am not able to open your .pbix file, it requires me to provide the relevant credentials in order to access it, so I think you can either turn off the credentials or use another method (Sharepoint) to upload your .pbix file.

Also I think you can create a bar chart that compares bid volumes between two intervals without using the summarize table option in Power BI, you can use DAX to create calculated columns or measures that dynamically respond to your date parameters. Here’s a DAX measure that you might find useful.

Bid Volume Comparison =
VAR CurrentInterval =
    MAX ( dataSAMPLE[INTERVAL] )
VAR PreviousInterval =
    CALCULATE ( MAX ( dataSAMPLE[INTERVAL] ), PREVIOUSMONTH ( dataSAMPLE[DATE] ) )
VAR CurrentBidVol =
    CALCULATE ( [BID_VOL], dataSAMPLE[INTERVAL] = CurrentInterval )
VAR PreviousBidVol =
    CALCULATE ( [BID_VOL], dataSAMPLE[INTERVAL] = PreviousInterval )
RETURN
    IF ( ISBLANK ( PreviousBidVol ), BLANK (), CurrentBidVol - PreviousBidVol )

Best Regards

Yilong Zhou

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