Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How do you calculate the average of [wholds] in the month? Activity date is connected to a date table.
Idealy, Id like to be able to know how many [wholds] a Sales rep made per day based off the number of days in the month.
Essentially it would be the disctinct count of wholds in the month divided by the number of days in the month, and I could put it into a matrix/table that would know which month the calculation corresponds to.
Wholds | Activity Date |
random numbers | 04-10-22 |
random numbers | 04-12-22 |
random numbers | 03-06-22 |
random numbers | 02-28-22 |
Solved! Go to Solution.
Hi @Anonymous
You can use
DIVIDE ( TableName[Wholds], COUNTROWS ( 'VALUES ( "Date'[Date] ) ) )
Hi @Anonymous ,
Please check the formula.
Meaasure =
VAR count_wholds =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Wholds] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Activity Date].[Year] = SELECTEDVALUE ( 'calendar'[Date].[Year] )
&& 'Table'[Activity Date].[Month] = SELECTEDVALUE ( 'calendar'[Date].[Month] )
)
)
RETURN
count_wholds / COUNTROWS ( 'calendar' )
Best Regards,
Jay
It did not work, I take it 'calendar' is the calendar table I have attached to the activity date.
Hi @Anonymous
You can use
DIVIDE ( TableName[Wholds], COUNTROWS ( 'VALUES ( "Date'[Date] ) ) )
This worked if the columns in the matrix are from the Date/Calendar table used and not from the Date from the Datatable.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |