In Tableau, how to calculate weighted average on a daily basis?

Question

My data looks like below:     
unit price    quantity     salesperson   date
$10             5               A         1/1
$10             6               B         1/1
$30             9               A         1/1
$30             10              B         1/1
$10             3               A         1/2
$10             5               B         1/2
$20             7               A         1/2
$20             8               B         1/2

In Tableau, how to calculate the average daily unit sales price each salesperson made? Suppose unit price and quantity are measures, salesperson and date are dimensions.

Thanks a lot!

Answer 1

I'll go ahead and give you the LOD expression, so that you'll have something that will work regardless of your table layout.

{ FIXED [salesperson], [date] : SUM([unit price] * [quantity]) / SUM([quantity]) }

That will give you a table that looks like this:

+------------+----------+-------------+------+-----------------+
| unit price | quantity | salesperson | date | Avg Daily Sales |
+------------+----------+-------------+------+-----------------+
| 10         | 5        | A           | 1/1  | 22.86           |
| 10         | 6        | B           | 1/1  | 22.5            |
| 30         | 9        | A           | 1/1  | 22.86           |
| 30         | 10       | B           | 1/1  | 22.5            |
| 10         | 3        | A           | 1/2  | 17              |
| 10         | 5        | B           | 1/2  | 16.15           |
| 20         | 7        | A           | 1/2  | 17              |
| 20         | 8        | B           | 1/2  | 16.15           |
+------------+----------+-------------+------+-----------------+

That follows the formula you gave in a comment on Nicarus's answer.

Here's a quick calculation, just to confirm that it works.

On 1/2, Salesman A sold:

  ( (10 * 3) + (20 * 7) ) / (3 + 7)
= (30 + 140) / 10
= 170 / 10
= 17

Answer 2

I think I figured it out: sum(unit price*quantity)/sum(quantity)

Answer 3

You want to determine the total amount (in dollars) sold on average, per day.

You can create a calculated field, like so:

SUM([Quantity] * [Unit Price]) / COUNTD([Date])

Then display the data by salesperson: