Forum Discussion
Average Transactions per Day
For some reason I am struggling to figure this out. By doing some searches, I have tried solutions using Group By, and by creating a measure column and adding a column, but for some reason, I am not able to get the solution I am expecting.
I have a large table each row contains a trailer along with a transaction date. The transaction date represents when that trailer either entered or left a geofence. I also have information on which location (city) and LoadGroup that the trailer is for. I can slice the data by those items, that is not my concern.
So, I have multiple entries per date, and I can have the same trailer on different dates.
What I want to determine is the average transactions per date. I can calculate a count per date, but for some reason when I go to calculate the average, that's where I hit a roadblock.
Here is a sample of the data;
| LoadID | LoadGroup | City | Trailer | Transaction Date |
| 65837536 | INBOUND | City A | 165143 | 6/1/2017 |
| 66256425 | INBOUND | City A | 255061 | 6/1/2017 |
| 66259429 | INBOUND | City B | 275084 | 6/1/2017 |
| 66377282 | INBOUND | City B | 255013 | 6/1/2017 |
| 66725986 | TRANSFER | City B | 245051 | 6/1/2017 |
| 66726004 | TRANSFER | City B | 255013 | 6/1/2017 |
| 66596336 | OUTBOUND BILLABLE | City C | 265077 | 6/1/2017 |
| 66596606 | OUTBOUND BILLABLE | City C | 145051 | 6/1/2017 |
| 66735849 | TRANSFER | City B | 165218 | 6/2/2017 |
| 66735964 | TRANSFER | City B | 115009 | 6/2/2017 |
| 66765536 | TRANSFER | City C | 265110 | 6/2/2017 |
| 66768794 | TRANSFER | City C | 165046 | 6/2/2017 |
| 66769410 | TRANSFER | City C | 115005 | 6/2/2017 |
| 66372780 | INBOUND | City D | 282002 | 6/2/2017 |
| 66771959 | OUTBOUND BILLABLE | City A | 165258 | 6/3/2017 |
| 66259485 | INBOUND | City B | 135193 | 6/3/2017 |
| 66377668 | INBOUND | City B | 255018 | 6/3/2017 |
| 66771843 | OUTBOUND BILLABLE | City B | 245011 | 6/3/2017 |
| 66771850 | OUTBOUND BILLABLE | City B | 274012 | 6/3/2017 |
| 66771957 | OUTBOUND BILLABLE | City C | 145017 | 6/3/2017 |
| 66774087 | TRANSFER | City D | 135127 | 6/3/2017 |
| 66505986 | INBOUND | City B | 135152 | 6/4/2017 |
| 66545207 | INBOUND | City B | 245053 | 6/4/2017 |
| 66590496 | OUTBOUND BILLABLE | City D | 135151 | 6/4/2017 |
| 66618324 | INBOUND | City D | 275069 | 6/4/2017 |
| 66619119 | OUTBOUND BILLABLE | City E | 235017 | 6/4/2017 |
Any help would be appreciated!
Thanks,
Jeff
I would think something along the lines of:
Average = COUNTROWS(Table) / DISTINCTCOUNT(Table[Transaction Date])
1 Reply
I would think something along the lines of:
Average = COUNTROWS(Table) / DISTINCTCOUNT(Table[Transaction Date])