topic Re: DAX Calculate Countrows, average per Year per Weekday in DAX Commands and Tips

DAX Calculate Countrows, average per Year per Weekday

bkoenen — Tue, 05 Apr 2022 18:16:44 GMT

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

Re: DAX Calculate Countrows, average per Year per Weekday

tackytechtom — Tue, 05 Apr 2022 19:59:26 GMT

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/

Re: DAX Calculate Countrows, average per Year per Weekday

bkoenen — Wed, 06 Apr 2022 07:47:08 GMT

Hello Tom,

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

Kind regards Björn

Re: DAX Calculate Countrows, average per Year per Weekday

tackytechtom — Wed, 06 Apr 2022 07:55:41 GMT

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/

Re: DAX Calculate Countrows, average per Year per Weekday

bkoenen — Wed, 06 Apr 2022 09:26:57 GMT

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

Re: DAX Calculate Countrows, average per Year per Weekday

daXtreme — Wed, 06 Apr 2022 12:33:14 GMT

[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

Re: DAX Calculate Countrows, average per Year per Weekday

bkoenen — Wed, 06 Apr 2022 13:26:12 GMT

Hello daXtreme,

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

Kind regards Björn

Re: DAX Calculate Countrows, average per Year per Weekday

daXtreme — Wed, 06 Apr 2022 14:42:53 GMT

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

Re: DAX Calculate Countrows, average per Year per Weekday

bkoenen — Thu, 07 Apr 2022 12:42:59 GMT

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.

Re: DAX Calculate Countrows, average per Year per Weekday

v-zhangti — Fri, 08 Apr 2022 08:22:48 GMT

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.