Forum Discussion
Need help with DAX Measure for Average Per Day
Okay, got it.
You can start by creating a general measure for your 'Good Leads' sum:Total Good Leads = SUM('Historical Data'[Good Leads])Then you can pass it into a measure that groups those sums and provides the average within the context of each unique date:
Average Good Leads Per Day = VAR distinctDates_TABLE = VALUES( Dates[Date] ) VAR result = AVERAGEX ( distinctDates_TABLE, [Total Good Leads] ) RETURN resultThe result of this value can then be used by itself, such as on a card visual, to return the total average of good leads per date, or you can use a matrix visual to further group the calculation within the context of other categories, such as inventory type, placement, month, etc.
rcarroll Thank you for asking that question and asking for clarification - I thought I had done that, but obviously not well enough. Yes, that example (which is a pivot table in excel) is the expected result. I have historical data for the past 6 years, with one column being called "Good Leads".
We want to show the average # of good leads provided for each inventory type per day. I have a date table set up as follows:
I am filtering the matrix for specific month year from that data table. Showing the previous year for the current month, as well as the past two months. Right now, my matrix just shows the average number of good leads, not the average per day. There is also slicer to select and filter results by store location.
I've tried the quick measures with co-pilot and just get the average, not the average per day, even though I include those words. I know in my excel brain, that it is the number of good leads divided by the number of days in that month, but I can't seem to get power bi to let me divide those two measures or calculate those measures. I hope that clarification helps. I appreciate any guidance you can provide.
Okay, got it.
You can start by creating a general measure for your 'Good Leads' sum:
Total Good Leads =
SUM('Historical Data'[Good Leads])
Then you can pass it into a measure that groups those sums and provides the average within the context of each unique date:
Average Good Leads Per Day =
VAR distinctDates_TABLE = VALUES( Dates[Date] )
VAR result = AVERAGEX (
distinctDates_TABLE,
[Total Good Leads]
)
RETURN result
The result of this value can then be used by itself, such as on a card visual, to return the total average of good leads per date, or you can use a matrix visual to further group the calculation within the context of other categories, such as inventory type, placement, month, etc.