How to find average daily sales per product per customer

[bknight15]

Hi there, looking to find the average daily sales per product per customer. the data is at individual sale level and there can be multiple sales per day.

Obtaining the total daily sales per product per customer is working (sumOver part of the field) but I am running into an issue with the average.

When there are two or more sales during the day, the field is calculating that multiple times when computing the daily average

avgOver(
        minOver(
                sumOver(
                        {sales},
                        [truncDate("DD",{sales_time}),{customer},{product}],
                        PRE_FILTER),
                [truncDate("DD",{sales_time}),{customer},{product}],
                PRE_FILTER
                ),
        [{customer},{product}],
        PRE_FILTER)

The result is the same with/without the minOver portion of the code, but that is what I am using to try and get one value per day included in the average calculation

Any help would be greatly appreciated

Thanks

[abacon]

Hi @bknight15,

Using some mock data (shown at the bottom of this post) I’ve built the following calculated field:
Average Daily Sales Per Product Per Customer

avgOver(
    Sales,
    [truncDate("DD",SalesTime),Customer,Product],
    PRE_FILTER
)

Adding this to a table visual I get the following result (comparing directly with an average of Sales in the Value field well):

This appears to validate the calculated field, does this meet your requirement?

Many Thanks,
Andrew

Column 1	Column 2	Column 3	Column 4
SalesTime	Customer	Product	Sales
17/09/2023 00:00:00	A	Prod A	1
17/09/2023 01:00:00	B	Prod B	2
17/09/2023 02:00:00	A	Prod A	3
17/09/2023 03:00:00	B	Prod B	1
17/09/2023 04:00:00	A	Prod A	2
17/09/2023 05:00:00	B	Prod B	1
17/09/2023 06:00:00	B	Prod A	2
17/09/2023 07:00:00	C	Prod B	3
17/09/2023 08:00:00	A	Prod A	4
17/09/2023 09:00:00	B	Prod B	1
18/09/2023 00:00:00	A	Prod A	2
18/09/2023 00:01:00	B	Prod A	3
18/09/2023 00:02:00	C	Prod A	1
18/09/2023 00:03:00	A	Prod B	2
18/09/2023 00:04:00	B	Prod A	3
18/09/2023 00:05:00	A	Prod B	4
18/09/2023 00:06:00	B	Prod A	1
18/09/2023 00:07:00	C	Prod A	2
18/09/2023 00:08:00	A	Prod A	1
18/09/2023 00:09:00	B	Prod B	2
19/09/2023 00:00:00	A	Prod A	1
19/09/2023 00:01:00	B	Prod B	2
19/09/2023 00:02:00	C	Prod A	3
19/09/2023 00:03:00	A	Prod B	1
19/09/2023 00:04:00	B	Prod A	1
19/09/2023 00:05:00	A	Prod B	2
19/09/2023 00:06:00	B	Prod A	3
19/09/2023 00:07:00	C	Prod B	4
19/09/2023 00:08:00	A	Prod A	1
19/09/2023 00:09:00	B	Prod B	2

[bknight15]

Hi Andrew,

Thanks for taking the time to reply.

I may have been a bit unclear in my question, but I am after the Average of total sales per day Per Product Per Customer rather than the Average of sales within the day.

For each day the calculated field would return the same value for each customer/product pair.

E.G. For Customer A/Prod A
total sales
17/9: 1+3+2+4 = 10
18/9: 2+1 = 3
19/9: 1+1 = 2

Average of total sales per day Per Product Per Customer
= (10+3+2) / 3
= 5

For all Prod A / Customer A rows, the calculated field would return 5

Once again any help would be greatly appreciated

Thanks
Bailey

[Ramon_Lopez]

Hi Baily

A function like this should give you the number you are looking for.

[Asem]

Hi there,
Did Ramons suggestion help? I’ll mark it as a solution, but please let us know if you needed further assistance with this question.
thanks.