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