Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |