Forum Discussion

bkoenen's avatar
bkoenen
Helper I
4 years ago
Solved

DAX Calculate Countrows, average per Year per Weekday

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...
COUNTROWS
dates
DAX
Weekday
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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's avatar
    v-zhangti
    4 years ago

    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.

bkoenen's avatar
bkoenen
Helper I
4 years ago

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's avatar
    daXtreme
    Solution Sage
    4 years ago
    [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's avatar
      bkoenen
      Helper I
      4 years ago

      Hello daXtreme,

       

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

       

       

      Kind regards Björn

      • daXtreme's avatar
        daXtreme
        Solution Sage
        4 years ago

        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