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 s...
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

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