DAX Calculate Countrows, average per Year per Weekday

[bkoenen]

Hello,

I think I have a simple question but I can't figure out how to do it.

I have a table in BI for example with a Date column and a column with the matching weekdays. There can be multiple equal dates because it's an order picking table.

I would like to have that it counts the rows from the same Weekday and divide it by the matching dates. So when I have 6 rows in total on Mondays and these Mondays matches two dates, the calculation should be 6 / 2 Then my average = 3 (check table as example)

What should be the correct DAX formula for this?

Date	Day of the week
2022-01-03	Monday
2022-01-03	Monday
2022-01-03	Monday
2022-01-03	Monday
2022-01-04	Tuesday
2022-01-04	Tuesday
2022-01-05	Wednesday
2022-01-05	Wednesday
2022-02-07	Monday
2022-02-07	Monday
2022-02-08	Tuesday
2022-02-09	Wednesday
2022-02-10	Thursday
2022-02-10	Thursday
etc. etc.
Average Countrows per Year per Weekday
	Result	Calculation
Monday	3	6 rows on Monday / 2 Dates
Tuesday	1,5	3 rows on Tuesday / 2 Dates
Wednesday	1,5	3 Rows on Wednesday / 2 Dates
Thursday	2	2 Rows on Thursday / 1 Date

With kind regards,

Björn Koenen

[tackytechtom · Accepted solution]

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/

[v-zhangti · Accepted solution]

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.

[tackytechtom · Accepted solution]

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/

[bkoenen]

Hello Tom,

Looks good, but I get everywhere the same result? Has it something to do with the MAXX function?

Kind regards Björn

[tackytechtom]

Hi @bkoenen ,

I created a measure instead of a calculated column 🙂

try a measure instead and see whether it works!

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

[bkoenen]

Hello Tom,

When I use the example with the Measure it works correct, but when I use it in the original report the average results are different then when you calculate it.

Left results are the picking orders per Monday, there are 13 Mondays. Calculation: 9392 / 13 = 722

Our calculation says there is an average of 2789 on Mondays? Any idea what goed wrong?

Kind regards Björn

[daXtreme]

[Your Column] = // a calc column, not a measure
var thisYear = year( T[Date] )
var dayOfWeek = T[Day of Week]
var RowsOfInterest =
    filter(
        T,
        year( T[Date] ) = thisYear
        &&
        T[Day Of Week] = dayOfWeek
    )    
var numOfSameWeekdays = countrows( RowsOfInterest )
var numOfDifferentDates =
    distinctcount(
        selectcolumns(
            RowsOfInterest,
            "@Date", T[Date]
        )
    )
var ratio = numOfSameWeekdays / numOfDifferentDates
return
    ratio

[bkoenen]

Hello daXtreme,

I get an error message for the Distinctcount function, any idea?

Kind regards Björn

[daXtreme]

Change to this:

[Your Column] = // a calc column, not a measure
var thisYear = year( T[Date] )
var dayOfWeek = T[Day of Week]
var RowsOfInterest =
    filter(
        T,
        year( T[Date] ) = thisYear
        &&
        T[Day Of Week] = dayOfWeek
    )    
var numOfSameWeekdays = countrows( RowsOfInterest )
var numOfDifferentDates =
    countrows(
        distinct(
            selectcolumns(
                RowsOfInterest,
                "@Date", T[Date]
            )
        )
    )
var ratio = numOfSameWeekdays / numOfDifferentDates
return
    ratio

[bkoenen]

Hello,

I did a test with a smaller amount of rows(20000). There are 2 dates on a Monday in the DATUM column, I took as return result only the VAR "numOfDifferentDates" to see the output. This gives 1704 and should be 2(Mondays) if we got this one correct and we put back the formula then it would be correct.