Let's assume I have 3 sales rep selling shoes, socks and trousers.
Sales rep 1 has sold
10 shoes
10 socks
Sales rep 2 has sold
10 shoes
10 trousers
Sales rep 3 has sold
20 shoes
20 trousers
My data is something like :
| Sales Rep | Shoes sold | Socks sold | Trousers Sold |
|---|
| 1 | 10 | 10 | |
| 2 | 10 | | 10 |
| 3 | 20 | | 20 |
And I want to represent this in a basic stacked bar chart representing the average number of each item sold by my sales reps
With this:
Dimension : Item Sold
Expression : sum(nbr items sold)/count(distinct sales rep)
I will get for the trousers part : 15 trousers (sold on average by the sales rep). This 15 represents the average number of trousers sold by my sales rep who actually sell trousers.
Now, some of my clients would like a different number : the number of trousers sold on average by my sales rep including those who don't actully sell trousers (in this case it would be 10 + 20 / 3 (sales rep) i.e. 10 (instead of 15).
How do I manage to do that in my bar chart? (it means dividing sum(nbr items sold) by the total number of sales rep including those who have sold an amount of 0)
Thanks in advance
