Forum Discussion

bkoenen's avatar
bkoenen
Icon for Helper I rankHelper 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 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?

 

DateDay of the week  
2022-01-03Monday   
2022-01-03Monday   
2022-01-03Monday   
2022-01-03Monday   
2022-01-04Tuesday   
2022-01-04Tuesday   
2022-01-05Wednesday   
2022-01-05Wednesday   
2022-02-07Monday   
2022-02-07Monday   
2022-02-08Tuesday   
2022-02-09Wednesday   
2022-02-10Thursday   
2022-02-10Thursday   
etc. etc.    
     
Average Countrows per Year per Weekday 
 ResultCalculation  
Monday36 rows on Monday / 2 Dates
Tuesday1,53 rows on Tuesday / 2 Dates
Wednesday1,53 Rows on Wednesday / 2 Dates
Thursday22 Rows on Thursday / 1 Date

 

With kind regards,

 

Björn Koenen

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.

9 Replies