Calculating Average of Sales per account in a raw data

[youssefm9]

Hello All,

I would appreciate your support on the below topic. 

I want to calculate the average sales value by Customer and per account per month and for the whole year.

To calculate the average value per customer and month it is straightforward (sum of sales value / countdistinct customer) and the month can be selected in the filter, and for all year it will give the average by selecting all months.

However, what is not working with me is to create a formula that provides me result of sales value per number of accounts per month and for all year, whether we select a customer or not. It is very tricky. I have tried to use DAX but did not work. If it is simply sum of sales value / sum of # of accounts, I think that wont make sense if we calculate for the whole year for all accounts. Is there any solution for it?

CUSTOMER	MONTH	SALES VALUE	# of ACCOUNTS
Customer A	Jan-22	200	10
Customer A	Feb-22	100	11
Customer A	Mar-22	20	12
Customer A	Apr-22	400	11
Customer A	May-22	200	12
Customer A	Jun-22	220	15
Customer A	Jul-22	120	14
Customer A	Aug-22	333	14
Customer A	Sep-22	593	14
Customer A	Oct-22	890	14
Customer B	Jan-22	900	5
Customer B	Feb-22	100	6
Customer B	Mar-22	50	6
Customer B	Apr-22	200	7
Customer B	May-22	100	7
Customer B	Jun-22	200	7
Customer B	Jul-22	300	7
Customer B	Aug-22	700	7
Customer B	Sep-22	400	8
Customer B	Oct-22	100	7
Customer C	Jan-22	200	23
Customer C	Feb-22	300	23
Customer C	Mar-22	230	23
Customer C	Apr-22	340	24
Customer C	May-22	450	24
Customer C	Jun-22	750	24
Customer C	Jul-22	230	24
Customer C	Aug-22	120	25
Customer C	Sep-22	400	24
Customer C	Oct-22	200	25

[youssefm9]

Hello Yolo.

Thanks for the reply. The solution does not work and this is the reason:

The number of accounts must not be commulative. If in Jan it was 5 accounts and Feb it is 6 accounts, then it has increased by 1 and not by 6. It is a subscribtion based sales value. So in PBI, if I filter customer A only, I want to see the average for full year (total sales value / total number of account (not commulative)). I can consider the average of the number of account during that year. Same goes for the number of account for all other customers.

So the best way is to first calculate the average sales per account for each month and then add these averages to see the total average sales per account.

Then if i want to see the total average for all the accounts, it will be average sales for each of the account divided by the number of accounts. 

Please let me know if it is not clear.

Thank you again. 

[v-xinruzhu-msft]

Hi @youssefm9 

You can create a measure:

Measure = CALCULATE(DIVIDE(SUM('Table'[SALES VALUE]),SUM('Table'[# of ACCOUNTS])),ALLSELECTED('Table'))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[youssefm9]

Hello Yolo,

Well it still does not do what is required.

The measure should be the sum of sales value / # of accounts 

# of accounts can be average of the full year or the # of accounts in last month.

What you did is you are dividing by commulative # of accounts, which should not be the case.

[v-xinruzhu-msft · Accepted solution]

Hi @youssefm9 

You can create a month column in table, then create two new table:

Table 2 = SUMMARIZE('Table',[CUSTOMER])
Table 3 = SUMMARIZE('Table',[month_Month])

Then create a new measure in table:

Average = SWITCH(TRUE(),ISFILTERED('Table 3'[month_Month])=FALSE()&&ISFILTERED('Table 2'[CUSTOMER])=FALSE(),CALCULATE(SUM([SALES VALUE])/SUM([# of ACCOUNTS]),ALLSELECTED('Table')),ISFILTERED('Table 2'[CUSTOMER])=TRUE()&&ISFILTERED('Table 3'[month_Month])=FALSE(),CALCULATE(SUM('Table'[SALES VALUE])/SUM('Table'[# of ACCOUNTS]),FILTER(ALLSELECTED('Table'),[CUSTOMER]=MAX('Table 2'[CUSTOMER]))),ISFILTERED('Table 2'[CUSTOMER])=FALSE()&&ISFILTERED('Table 3'[month_Month])=TRUE(),CALCULATE(SUM('Table'[SALES VALUE])/SUM('Table'[# of ACCOUNTS]),FILTER(ALLSELECTED('Table'),MONTH('Table'[MONTH])=MAX('Table 3'[month_Month]))),CALCULATE(SUM('Table'[SALES VALUE])/SUM('Table'[# of ACCOUNTS]),FILTER(ALLSELECTED('Table'),MONTH('Table'[MONTH])=MAX('Table 3'[month_Month])&&[CUSTOMER]=MAX('Table 2'[CUSTOMER]))))

create a measure to set if the measure can display:

if_display = SWITCH(TRUE(),ISFILTERED('Table 3'[month_Month])=FALSE()&&ISFILTERED('Table 2'[CUSTOMER])=FALSE(),1,ISFILTERED('Table 3'[month_Month])=FALSE()&&ISFILTERED('Table 2'[CUSTOMER])=TRUE(),IF(MAX('Table'[CUSTOMER])=MAX('Table 2'[CUSTOMER]),1,0),ISFILTERED('Table 3'[month_Month])=TRUE()&&ISFILTERED('Table 2'[CUSTOMER])=FALSE(),IF(MAX('Table'[month_Month])=MAX('Table 3'[month_Month]),1,0),IF(MAX('Table'[CUSTOMER])=MAX('Table 2'[CUSTOMER])&&MAX('Table'[month_Month])=MAX('Table 3'[month_Month]),1,0))

Then put the table 2 and table 3 columns to two slicers, and put the if_display measure to the table visual filter:

Output:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.