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 ID Product Vendor SalesDate Sales Hour 304 ProdA VendA...
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.
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.