Distinct Count in Power Pivot for Data analyst book

[Abhay Gadiya]

I am currently reading Power Pivot for Data Analyst book by Mr. Excel. In Chapter 10 he explains about "Countrows(Distinct(Sales[Customer]))" function. Later using this function average sales per customer is calculated using measure "=Sum(Sales[Revenue])/Sales[DistinctCustomers]".

I noted that this average sales per customer (Avg.) measure gave correct values for all combination in pivot except when I put the customer names in "Row" area. Then it gave "Total Sales" for that customer.

Is there a different measure formula we should use which will show both average sales per customer and also average sales customer wise.

You can refer to file "10-Distinct.xlsx" available at Que publishing site
PowerPivot for the Data Analyst: Microsoft Excel 2010 | Que

 

[RoryA]

Perhaps:

=if(Sales[DistinctCustomers]=1,average(Sales[Revenue]),Sum(Sales[Revenue])/Sales[DistinctCustomers])

 

[scottsen]

So... Total Sales / #Customers is the SalesPerCustomer. When there is only 1 customer, that is TotalSales/1 which is TotalSales. It sounds like it all works as designed.

But you are saying... if there is only 1 customer you want... something else? What are you looking for? " also average sales customer wise." means what to you? Per day? per transaction? Per Item?

 

[Abhay Gadiya]

Thank you...This works perfectly.

 

[Abhay Gadiya]

So... Total Sales / #Customers is the SalesPerCustomer. When there is only 1 customer, that is TotalSales/1 which is TotalSales. It sounds like it all works as designed.

But you are saying... if there is only 1 customer you want... something else? What are you looking for? " also average sales customer wise." means what to you? Per day? per transaction? Per Item?

As RoryA gave the formula it works like this:

1. If there is a customer A - it will calculate average sales for that customer only.
2. If we have one more level - "Region", etc. grouping these customers it will give average considering distinct customer in that region.

I hope this clarifies. Or try putting customer in Pivot table created in above mentioned file. And then using formual given by RoryA

 

[RoryA]

I should point out that if you only have one customer in a particular level (e.g. region) you will also get the average sales for that customer, rather than the average total sales per customer. I think you'd be better off with different measures, and perhaps a clearer name for the existing measure.