Slicer value for calculating average

[Stanil]

Hello,

as my problem is more complex, I need to present some sample data.

Table "sales" contains this sample data:

Transaction ID	Product	Vendor	SalesDate	Sales Hour
304	ProdA	VendA	2022-03-04	18:00:00
305	ProdB	VendB	2022-03-06	19:00:00
306	ProdA	VendA	2022-03-07	12:00:00
307	ProdC	VendC	2022-03-09	14:00:00
308	ProdB	VendB	2022-03-11	12:00:00
309	ProdA	VendA	2022-03-14	13:00:00

The last column is calculated from the SalesDate.

I have two slicers - first selecting Vendors, second selecting time frame of the SalesDate.

What I want to achieve is the bar chart with hours of the day on the X-axis, and the calculated AVERAGE COUNT of sales for the given hour, for selected  (via slicers) timeframe and vendor.

I created such visual, but I have two problems:

 - if I select a vendor whose things were sold mainly on afternoons, some hours will not appear on the chart at all, even with "0" value. I'd like to be able present the full 0-23 hour range on the chart.

 - more importantly, I get the wrong data. If I select timeframe of two days, and there will be just one sale on 12:00, I expect the average value to be 0.5 (for this hour), but it is calculated as 1. It turns out the average calculation does NOT take into account the days the sales did not happen at all. If I select the timeframe of 3 days, I need to calculate the average of three days, regardless how many days ended with some sales or not.

My average calculation is rather simple:

myavg =

var firstday = min(sales[SalesDate])

var lastday = min(sales[SalesDate])

return

 count(sales[TransactionID]) / datediff (firstday, lastday, day)

I guess I need to learn more about slicers and using their values in the formula, but I could not find too much iinformation - most of the tutorials are rather basic about using the visuals and creating first data manipulation. Do you know the good intermediate-level PowerBI training?

[Stanil · Accepted solution]

It works! however I must reveal your "secret ingredient" - you modified the calculating the date table, from

Date =

VAR MinYear = YEAR ( MIN ( 'Facts20'[SalesDate] ) )

VAR MaxYear = YEAR ( MAX ( 'Facts20'[SalesDate] ) )

RETURN

ADDCOLUMNS (

    FILTER (

        CALENDARAUTO( ),

        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

    )

)

to

Date =

VAR MinYear = MIN ( 'Table'[SalesDate] )

VAR MaxYear = MAX ( 'Table'[SalesDate] )

RETURN

ADDCOLUMNS (

    CALENDAR(MinYear,MaxYear)

)

And that is the most important change you did. 🙂

Thank you.

[v-yalanwu-msft]

Hi, @Stanil ;

You could modify to this:

myavg2 = 
var firstday = CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'))
var lastday =CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))
return
 count('Table'[Transaction ID]) / (datediff (firstday, lastday, day) + 1)   

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stanil]

Hi, thank you very much for the effort of resolving my issue. Unfortunately, it is not resolved yet.

Please add one line to your source file for Facts20 -

322

ProdA

VendC

2022-03-06

14:00:00

Now, if we select three days between March 4 and March 6, and select Vendor C, we see average of 1, while I expect it to be 0.3333 (because out of three days selected, there was one transation on 2 pm, so the average is 1 count /3 days).

Also, the chart shows only the one hour then, not the full range from 0 to 23, but this is minor issue.

[mangaus1111]

Hi @Stanil ,

I have done some corrections to your measure

myavg = 

var firstday = CALCULATE(min('Facts20'[SalesDate]),ALL('Time'[Hour]))

var lastday = CALCULATE(max('Facts20'[SalesDate]),ALL('Time'[Hour]))

return
 count('Facts20'[Transaction ID]) / (datediff (firstday, lastday, day) + 1)  

See my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJi1RzgFuQHajV2Xdf?e=02yiie

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