cancel
Showing results for
Did you mean:  Helper I

## Average Revenue per Unique Customer

Hi everyone,

I am trying to generate an Average Revenue per Customer column in Power BI. I have data on each sale made to a customer, including their Customer ID, the revenue generated from the sale, which Department the sale was made in and the type of subscription package was purchased.

Since some customers only buy one item and others buy multiple, the Average Revenue per Customer must be per unique customer.

If I use the 'average' function from the drop-down menu in the 'Values' section, I assume it divides the total revenue by the count of revenue fields. I want the total divided by the unique number of customers.

1 ACCEPTED SOLUTION  Helper I

Hi all,

Solution was much simpler than I thought and was a 2 step solution.

All I needed to do was create one measure: Distinctcount Customers = DISTINCTCOUNT(Customer ID)

Then I created a second measure which used this measure in it:

Average Revenue per Customer = SUM(Invoices[Revenue])/[Distinctcount Customers]

I am still very much new to the software, so apologies for missing this one!

Thanks,

Greg

6 REPLIES 6  Microsoft

I assume you have a dataset as below. If you want to get the average sales per customer, you can use following formula. ```Average_Per_Customer =
CALCULATE (
AVERAGE ( MyData[Sales] ),
ALLEXCEPT ( MyData, MyData[Customer ID] )
)``` If you want to get the average sales of all customers, you can use another formula as below.

```Average_All_Customers =
CALCULATE (
SUM ( MyData[Sales] ) / DISTINCTCOUNT ( MyData[Customer ID] ),
ALL ( MyData )
)``` Best Regards,

Herbert  Responsive Resident

Create a custom measure by using DAX.... something like this

newMeasure = CALCULATE(SUM(Sales))/DISTINCTCOUNT(Customers), ALLSELECTED(YourTable)  Helper I

Thanks for the suggestions so far guys. I have had some success but not completely right yet.

I am using this formula: Average Revenue Customer = Invoices_Feb[Revenue]/DISTINCTCOUNT(Invoices_Feb[Customer_ID])

The Revenue Column is auto-summed in Power BI so I did not include it in the DAX formula.

This has given me the correct Average revenue per customer (ARC) for the overall ARC (ARC for all customers across all departments and all subscription packages). However, the ARC values for the individual departments and subscription packages are incorrect - they are all too small.

I have found that when Power BI is calculating the ARC for a subset of the total customers (i.e. ARC for customers who bought products from one department), it correctly calculates the revenue for that department but then divides that number by the total number of customers.

If this is unclear, here is an example of what is happening in Power BI with made up numbers:

Sum of total revenue = \$80,000

Distinct count of all customers = 4000

ARC across all departments = 80,000/4000 = \$20

So far, this is all correct. The problem occurs below once a subset of customers/revenue is used:

What I Want to Happen:

Sum of revenue from storage = \$15,000

Distinct count of customers who bought storage = 1,500

ARC of customers who bought storage = 15,000/1,500 = \$10

What Power BI does:

ARC of customers who bought storage = 15,000/4000 = \$3.75

As you can see, the revenue derived from sales of storage has been calculated correctly, but it is then divided by the distinct count of total customers, not only customers who bought storage, thus returning a lower ARC because the total number of customers is larger than the subset of storage customers.

I hope this is clear!

Thanks,

Greg  Microsoft

Please try with following measure formulas.

```Average_Per_Depart =
DIVIDE (
CALCULATE ( SUM ( MyData[Sales] ), ALLEXCEPT ( MyData, MyData[Department] ) ),
CALCULATE (
DISTINCTCOUNT ( MyData[Customer ID] ),
ALLEXCEPT ( MyData, MyData[Department] )
)
)```
```Average_Per_Package =
DIVIDE (
CALCULATE ( SUM ( MyData[Sales] ), ALLEXCEPT ( MyData, MyData[Subscription Package] ) ),
CALCULATE (
DISTINCTCOUNT ( MyData[Customer ID] ),
ALLEXCEPT ( MyData, MyData[Subscription Package] )
)
)``` Best Regards,

Herbert  Helper I

Hi all,

Solution was much simpler than I thought and was a 2 step solution.

All I needed to do was create one measure: Distinctcount Customers = DISTINCTCOUNT(Customer ID)

Then I created a second measure which used this measure in it:

Average Revenue per Customer = SUM(Invoices[Revenue])/[Distinctcount Customers]

I am still very much new to the software, so apologies for missing this one!

Thanks,

Greg Frequent Visitor

Greg, i guess you shall filter calculations of customers, something like CALCULATE(DISTICTCONT(Table[CustomerID], CALCUALATETABLE(relatedTable, relatedTable[Column]>0)) - whatever criteria you have. Just an idea, didn't play with your model. Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (3,046)