I am looking for a way to create an average basket size by date
Assuming I have data set that looks like this:
Invoice | Date | SKU | Sold Price |
111 | Jan 4 | Product A | 10 |
111 | Jan 4 | Product B | 20 |
112 | Jan 4 | Product C | 5 |
113 | Jan 4 | Product A | 10 |
113 | Jan 4 | Product B | 20 |
113 | Jan 4 | Product C | 5 |
114 | Jan 20 | Product A | 10 |
115 | Jan 20 | Product A | 10 |
115 | Jan 20 | Product C | 5 |
116 | Jan 20 | Product D | 45 |
In the example above the Average basket size during Jan 4 would be the average of invoices 111, 112, and 113: (30 + 5 + 35) / 3 = 23.333
Whereas on Jan 20, the average basket size was average of invoices (114,115,116): (10+5+45)/3 = 20
The goal would be to ultimately create a chart that will show what the average basket size is over time (likely grouping the individual dates by week), but before I get to that point, I assume I need to do a beastmode calculation to actually populate those values first