Forum Discussion

GCGradwell's avatar
GCGradwell
Icon for Helper I rankHelper I
10 years ago
Solved

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

Need Help
  • GCGradwell's avatar
    GCGradwell
    10 years ago

    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

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

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

  • Habib's avatar
    Habib
    Icon for Continued Contributor rankContinued Contributor
    10 years ago

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

     

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

    • GCGradwell's avatar
      GCGradwell
      Icon for Helper I rankHelper I
      10 years ago

      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's avatar
        SergeiStPete
        Icon for Helper I rankHelper I
        10 years ago

        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.

  • muruganselva's avatar
    muruganselva
    Frequent Visitor
    1 year ago
    Average Revenue per Customer = SUM(Orders[Sales])/COUNT(Orders[Customer ID])
  • muruganselva's avatar
    muruganselva
    Frequent Visitor
    1 year ago
    Average_sales_Per_Customer =
    DIVIDE (
        CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) ),
        CALCULATE (
            COUNT(  Orders[Order ID] ),
            ALLEXCEPT ( Orders, Orders[Customer ID] )
        )
    )