DAX Calculate Countrows, average per Year per Weekday

Hi bkoenen ,

How about this:

Here the measure:

Average Countrows per Year per Weekday = 
VAR _helpTable = 
SUMMARIZE (
    Table,
    Table[Day of the week],
    "NumberOfDayOfTheWeek", COUNT ( Table[Day of the week] ),
    "DistinctNumberOfDates", DISTINCTCOUNT ( Table[Date] )
) 
RETURN
MAXX (_helpTable, [NumberOfDayOfTheWeek] ) / MAXX (_helpTable, [DistinctNumberOfDates] ) 

And here how the helpTable would look like:

Let me know if this helps or if you have any questions 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Hi, bkoenen 

You can try the following methods.

Measure:

Result = 
VAR _N1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            [Day of the week] = SELECTEDVALUE ( 'Table'[Day of the week] )
        )
    )
VAR _N2 =
    CALCULATE (
        COUNT ( 'Table'[Day of the week] ),
        FILTER (
            ALL ( 'Table' ),
            [Day of the week] = SELECTEDVALUE ( 'Table'[Day of the week] )
        )
    )
RETURN
    DIVIDE ( _N2, _N1 )

COUNT: https://docs.microsoft.com/dax/count-function-dax 

DISTINCTCOUNT: https://docs.microsoft.com/dax/distinctcount-function-dax 

Best Regards,

Community Support Team _Charlotte

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