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

Accepted Solutions
GCGradwell Helper I
Helper I

Re: Average Revenue per Unique Customer

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
Habib Responsive Resident
Responsive Resident

Re: Average Revenue per Unique Customer

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

 

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

Microsoft v-haibl-msft
Microsoft

Re: Average Revenue per Unique Customer

@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

GCGradwell Helper I
Helper I

Re: Average Revenue per Unique Customer

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

SergeiStPete
Frequent Visitor

Re: Average Revenue per Unique Customer

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.

Microsoft v-haibl-msft
Microsoft

Re: Average Revenue per Unique Customer

@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

GCGradwell Helper I
Helper I

Re: Average Revenue per Unique Customer

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors