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...
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):

     

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,