1 2 Previous Next 15 Replies Latest reply on Jul 26, 2017 7:59 AM by Arthur Tang

# Average Revenue per Unit

Hi,

I'm having an issue with using Tableau to calculate Average Revenue per Unit.  The data has 5 records for the same account, one for each business segment.  The problem is it sums the "Units" (the denominator in the average revenue per unit calculation) 5 times and I want to be able to see ARPU by Business Segment excluding units when there is no revenue.

For example I have an account that does business in 3 of the 5 Business Segments.  When I create the calculated field of sum(revenue) / sum(units) and make the aggregate measure an average it still includes the units where there is no revenue.  I included a depiction of my issue.  The red highlighted rows I do not want to included in the calculation of ARPU.  If someone could help me out it would be greatly appreciated.

Thanks!

 Customer Units Business Segment Revenue Customer One 20 A \$50 Customer One 20 B Customer One 20 C \$100 Customer One 20 D \$25 Customer One 20 E
• ###### 1. Re: Average Revenue per Unit

Upload a sample TWBX with some sample data extracted within it.  To do so, simply put some raw data into Excel, and connect to that in Tableau, then post the TWBX with the data extracted here.  That way, we can visually see your data and interact with Tableau to get you a solution that works.

Let me know if you need help with posting a TWBX.  Once its ready, click "Use advanced editor" in the top right corner of you reply, then @ Mention will appear in the bottom right, next to "@ Mention"

• ###### 2. Re: Average Revenue per Unit

I'm with Matt on the TWBX.

But I'm guessing you want to divide by something like SUM(if not(isnull([revenue])) then [units] end).  (Or you could probably do the same with COUNT instead of SUM.)

Chances are, that equation is going to need some tweaking once you try to implement it, which is why having a TWBX for us all to play together in will be valuable.

• ###### 3. Re: Average Revenue per Unit

I attached a sample TWBX and the excel file it is connected to.  When using Tableau, I put ARPU by Business Segment and for Business Segment A Tableau calculates it as 2.414 vs if you look in excel it should be 2.59

• ###### 4. Re: Average Revenue per Unit

Daniel Dunleavy wrote:

...if you look in excel it should be 2.59

I'm not sure what you mean by this.  Can you provide a mock-up of your expected results, in terms of what you hope to see in Tableau?  I believe I understand what you want, but it will help others if you can mock up your expected results.

It would also be helpful if you can explain the logic behind the 2.59 value you are expecting.

• ###### 5. Re: Average Revenue per Unit

I reattached the excel file with what the numbers should be highlighted in a yellow table along with the ARPU calculation in column E.  The 2.59 ARPU for Business Segment A is taking the average of ARPU for business segment A.

Thank you!

• ###### 6. Re: Average Revenue per Unit

Let me see if I have this right.

For Unit-A you have 3 rows of consequence:

\$50 from 20 units

\$200 from 40 units

\$100 from 35 units.

You want to divide 350 by 95 units.  But that comes to 3.68 per unit.

• ###### 7. Re: Average Revenue per Unit

Correct.  The formula in the excel for ARPU (column E) is column D (revenue) divided by Column B (units) then I need to take the average of those results.  Basically the average of the average revenue per bed

• ###### 8. Re: Average Revenue per Unit

so would 3.68 be the correct result for business Unit-A?

• ###### 9. Re: Average Revenue per Unit

Joe you are correct, that is the number I am looking for

• ###### 10. Re: Average Revenue per Unit

Change your calc field to look like this:

sum([Revenue])/sum(if not(isnull([Revenue])) then [Units] end)

• ###### 11. Re: Average Revenue per Unit

that basically says to exclude customers with no revenue.

• ###### 12. Re: Average Revenue per Unit

Okay I will give it a try.  The only thing is my units is a dimension and must remain a dimension because I group the units as a quick filter.  Will that have any effect?

• ###### 13. Re: Average Revenue per Unit

I did it on your workbook.  That's where I got the correct number from.  I cut and pasted the modified calc equation into the previous reply.

• ###### 14. Re: Average Revenue per Unit

It works! Thank you very much

1 2 Previous Next