Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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.
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.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hello Tom,
Looks good, but I get everywhere the same result? Has it something to do with the MAXX function?
Kind regards Björn
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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
[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
Hello daXtreme,
I get an error message for the Distinctcount function, any idea?
Kind regards Björn
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |