Forum Discussion

Stanil's avatar
Stanil
Regular Visitor
3 years ago
Solved

Slicer value for calculating average

Hello,

 

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

Table "sales" contains this sample data:

Transaction IDProductVendorSalesDateSales Hour
304ProdAVendA2022-03-0418:00:00
305

ProdB

VendB2022-03-0619:00:00
306ProdAVendA2022-03-0712:00:00
307ProdCVendC2022-03-0914:00:00
308ProdBVendB2022-03-11

12:00:00

309ProdAVendA2022-03-1413: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?

Need Help
Tutorial Requests
  • Stanil's avatar
    Stanil
    3 years ago

    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.

4 Replies

  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    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.

  • Stanil's avatar
    Stanil
    Regular Visitor
    3 years ago

    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 -

    322ProdAVendC2022-03-0614: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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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's avatar
    Stanil
    Regular Visitor
    3 years ago

    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.