Average Sales Per customer not working

[san17680]

Hi All,

i have tried to get avg sales per cust, but its not working, it somehow shows total sales.

here is my formula

AVG SALES PER CUST = AVERAGEX(VALUES(Customer[Customer]),SUM(Sales_data[Sales Amount]))

i also tried,  

AVG SALES PER CUST = AVERAGEX(VALUES(Customer[Customer]),CALCULATE(SUM(Sales_data[Sales Amount])))

it did not work as well.

Thanks

[mlsx4 · Accepted solution]

But total sales measure sums the values as in option 2, isn't it?

Then, it wouldn't be possible to achieve result in 1

[mlsx4]

Hi @san17680 

Change the sum of the calculate by the average:

AVG SALES PER CUST = AVERAGEX(VALUES(Customer[Customer]),CALCULATE(AVERAGE(Sales_data[Sales Amount])))

[san17680]

Thanks for your response

when i calculate 

AVG SALES PER DAY = AVERAGEX(VALUES('Calendar'[Date]),[TOTAL_SALES])

I use sales measure

why do we use average inside averagex as you suggested,

i thought averagex would iterate on each customers sales and then average all of the rows in the formula [AVG SALES PER CUST = AVERAGEX(VALUES(Customer[Customer]),SUM(Sales_data[Sales Amount]))]?

is this correct understanding?

Thanks

[mlsx4]

Hi @san17680 

You need first to calculate the average for each customer (AVG inside the calculate) and then if you want the average for the whole column, then you need to use AVERAGEX which will evaluate the expression row by row. If you sum, it will compute the SUM for each customer and later on, the AVERAGEX will compute the average over that sum.

Sorry in advance if I haven't explained it clearly

[san17680]

Thanks for clarifying. my question is

why do we use sales measure in this formula then?

AVG SALES PER DAY = AVERAGEX(VALUES('Calendar'[Date]),[TOTAL_SALES])

aren't avg sales per day and avg sales per customer one and same thing?

Thanks

[mlsx4]

Sorry but I have lost myself. Are you using two different measures? Because the one I answered first was related to customers 

[san17680]

i think I did not make myself clear.

this is not related to current question, but when i do avg sales per day here is formula for that 

AVG SALES PER DAY = AVERAGEX(VALUES('Calendar'[Date]),[TOTAL_SALES])

it uses a sales measure.

Now coming to the question,

for Avg sales per customer why are we using average in measures and not sales like AVG SALES PER DAY. i am confused about this. 

Thanks

[mlsx4]

Well, I just tried to correct your formula, but I don't really now what you are trying to achieve or which is your expected output. Anyway, I think it should work if you use total sales too. It's difficult for me to realize your expectations without the data/expected result. What are you trying to achieve 1 or 2?

[san17680]

i m trying to achieve 1 but not using average in measuers, instead i want to use total sales measure, is this possible

Thanks

[mlsx4 · Accepted solution]

But total sales measure sums the values as in option 2, isn't it?

Then, it wouldn't be possible to achieve result in 1

[san17680 · Accepted solution]

ok thanks for your time.