cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GCGradwell
Helper I
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.

 

Thanks in advance

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
v-haibl-msft
Microsoft
Microsoft

@GCGradwell

 

I assume you have a dataset as below. If you want to get the average sales per customer, you can use following formula.

Average Revenue per Unique Customer_1.jpg

Average_Per_Customer =
CALCULATE (
    AVERAGE ( MyData[Sales] ),
    ALLEXCEPT ( MyData, MyData[Customer ID] )
)

Average Revenue per Unique Customer_2.jpg

 

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 )
)

Average Revenue per Unique Customer_3.jpg

 

Best Regards,

Herbert

Habib
Responsive Resident
Responsive Resident

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

 

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

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

@GCGradwell

 

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] )
    )
)

Average Revenue per Unique Customer_1.jpg

 

Best Regards,

Herbert

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

View solution in original post

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.

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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