Forum Discussion
Need help with DAX Measure for Average Per Day
Hello, I've been working in Power BI only a few months.. I am trying to create a matrix that shows the average number of good leads by Inventory Product Type, placement type and then columns are filtered on certain months. There are currently hundreds of rows for each product/placement type. I've attached a sample spreadsheet. I can get the "average" # of the good leads, but not the average per day. The months are the previous year month, then the last two months, as well as the current month to date. Currently, we show the average of the last three current months (past two + mtd), and then compare the current mtd to the average to see the percentage off from the average. We will probably lose the MTD information for YoY and MoM visuals, but I still need to know how to get the average per day. Are there any other measures I need to ceate? I did create a number of days in the month measure to use but I can't seem to select it in a divide function. You guys are great so thanks in advance for any guidance you can provide.
I've attached the sample data here:
| Average Good Leads Per Day | |||||||
| Placement | 2023-06 | 2024-05 | 2024-06 | 2024-07 MTD | AVG | % off | |
| Prod Type 1 | 2.23 | 7.16 | 7.80 | 6.70 | 7.22 | -7.25% | |
| Dealer | 0.30 | 0.77 | 0.37 | 0.35 | 0.50 | -29.91% | |
| Listing | 1.10 | 2.23 | 3.20 | 2.39 | 2.61 | -8.23% | |
| Misc | 0.07 | 0.03 | - | - | 0.01 | -100.00% | |
| Website | 0.77 | 4.13 | 4.23 | 3.96 | 4.11 | -3.65% | |
| Prod Type 2 | 0.23 | 1.29 | 1.47 | 1.87 | 1.54 | 21.23% | |
| Listing | - | 0.26 | 0.23 | 0.39 | 0.29 | 32.99% | |
| Website | 0.23 | 1.03 | 1.23 | 1.48 | 1.25 | 18.46% | |
| Prod Type 3 | 0.43 | 9.45 | 12.37 | 8.61 | 10.14 | -15.12% | |
| Deaer | - | 0.06 | 0.03 | - | 0.03 | -100.00% | |
| Listing | 0.30 | 5.77 | 7.03 | 4.30 | 5.70 | -24.54% | |
| Misc | - | - | - | - | - | 0.00% | |
| Website | 0.13 | 3.61 | 5.30 | 4.30 | 4.41 | -2.30% | |
| TOTAL | 2.90 | 17.90 | 21.63 | 17.17 | 18.90 | -9.15% |
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.
6 Replies
Hi jfowings1 ,
That doesn't seem to be the underlying data, and forming that matrix seems to have to have 40 rows of data and provide the expected results you want.
Best regards,
Community Support Team_ Scott ChangWhen you say "sample data", do you really mean to say "this is an example of the final expected results", or are you saying that this is the actual underlying dataset that you are working from? This looks more like a pivot table in Excel or a Matrix visual in Power BI. If that isn't the raw data, then I suggest you give an example of what the raw data is, with the column headers and values for each column. This will make it easier to try to help you out.
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 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.
