Forum Discussion

Dn_wemn's avatar
Dn_wemn
Icon for Helper II rankHelper II
3 years ago
Solved

Average measure per Weekday selection: Handling no data

Hi all,

 

I'd like to have an average visits measure per weekday selection for this case: When I select Monday and Tuesday and see there are no data fro Tuesday, the average measure should calculate Tuesday as well:

For this example in the screenshot, the avg result should be 44 / 2 (selected days) = 22

and not 44. How can this be achieved?

 

My current measure =

AVERAGEX(
    KEEPFILTERS(VALUES('Table'[Weekday text])),
    CALCULATE( [Visits] + 0 )
)

Thank you!

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Dn_wemn ,

     

    I think you issue should be caused that data table do not have "Tuesday" value. Here I suggest you to create a continuous DimDate table to help you calculate.

    DimDate:

    Date =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Start Time ] ), MAX ( 'Table'[Start Time ] ) ),
    "DayDesc", FORMAT ( [Date], "DDDD" ),
    "Day", WEEKDAY ( [Date], "2" )
)

    Relationship:

    Use the [DayDesc] column in that 'Date' column.

    My current measure =
AVERAGEX (
    KEEPFILTERS ( VALUES ( 'Date'[DayDesc] ) ),
    CALCULATE ( [Visits] + 0 )
)

     Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

3 Replies