Forum Discussion

Aho00's avatar
Aho00
Regular Visitor
9 months ago
Solved

Average by weekday (even without data)

Hello, I need some help. Thanks in advance for your help.

Lets say I have 2 tables in Power BI

Table1 :

 


Calendar (linked to table1 by the date and lets assume it continues until end of 2025) :

 

I have a slicer to select the month : September
Also an other one to select the year : 2025
I would like the average number of machine for each weekday. It also need to include day without data. I will use a bar chart to visualise it

Actually, I'm able to do the average only for each weekday with data.
For example, for monday I have 6/2 =3 -> 6 row of data 2 monday from the table
for wednesday, i have 5/2=2.5 ->5 row of data, 2 wednesday from table

But I would like to do the average if there is no data for some day (for example 15/09/2025 is a monday but I have no data for it)

 

For exemple, for monday it should be 6/5=1.2 ---> 6 row of data with monday but there is 5 monday in september 2025
for Wednesday it should be 5/4=1.25 --> 5 row of data with wednesday but there is 4 wednesday in september 2025

Thanks.

Need Help
  • KarinSzilagyi's avatar
    KarinSzilagyi
    9 months ago

    Hi Aho00, you need to divide the number of machine rows by how many times that weekday actually appears in your selected month/year (based on your Calender table), not just by the number of days where you have data.

    Try this:

    Average Machines per Weekday =
VAR WeekdayName =
    SELECTEDVALUE ( 'Calendar'[Weekday] )

VAR DaysOfThisWeekday =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Date] ),  
        REMOVEFILTERS ( Table1 ),            
        KEEPFILTERS ( 'Calendar'[Weekday] = WeekdayName ),
        ALLSELECTED ( 'Calendar'[Date] )      
    )

RETURN
DIVIDE ( COUNTROWS ( Table1 ), DaysOfThisWeekday, 0 )

     

    Use your Weekday-column from your Calendar-dimension on your bar chart axis and turn on "show items with no data" (right-click on the field in the X-Axis to show this dialogue):

     

4 Replies

  • KarinSzilagyi's avatar
    KarinSzilagyi
    Super User
    9 months ago

    Hi Aho00, you need to divide the number of machine rows by how many times that weekday actually appears in your selected month/year (based on your Calender table), not just by the number of days where you have data.

    Try this:

    Average Machines per Weekday =
VAR WeekdayName =
    SELECTEDVALUE ( 'Calendar'[Weekday] )

VAR DaysOfThisWeekday =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Date] ),  
        REMOVEFILTERS ( Table1 ),            
        KEEPFILTERS ( 'Calendar'[Weekday] = WeekdayName ),
        ALLSELECTED ( 'Calendar'[Date] )      
    )

RETURN
DIVIDE ( COUNTROWS ( Table1 ), DaysOfThisWeekday, 0 )

     

    Use your Weekday-column from your Calendar-dimension on your bar chart axis and turn on "show items with no data" (right-click on the field in the X-Axis to show this dialogue):

     

  • DataNinja777's avatar
    DataNinja777
    Super User
    9 months ago

    Hi Aho00, 

    You can create your required output by creating a measure like below:

    Average per Weekday = 
VAR TotalEntries =
    COUNTROWS ( 'Table1' )
VAR TotalWeekdaysInPeriod =
    COUNTROWS ( 'Calendar' )
RETURN
    DIVIDE ( TotalEntries, TotalWeekdaysInPeriod, 0 )

    Then, as KarinSzilagyi mentions, in the bar chart visual Weekday Y-axis field, right click and select "Show items with no data".  The resultant output is as shown below:

     

     

     

    I attach an example pbix file for your reference.

    Best regards,