Apologies if this has been answered elsewhere already, my searches didn't come up with anything addressing my situation fully.
I have a data pull where there are multiple variables that I would like to have considered in the weighted average. The variables are:
1. Time period (Monthly)
2. Vendor (Corporate/Partner)
3. Sales Channel (Phone, Retail, Online)
4. Sales Region (Western, Eastern, Central, International)
My values are just Revenue and Units Sold, split out by each of the above variables.
Currently my formula in Tableau is
sum(([Rev]/[Units Sold])*[Units Sold])/sum([Units Sold])
and gets me quite close in most cases to what the Excel sumproduct provides for each group or group of variables, but it isn't exact and in some regions it doesn't get close at all. It appears to be aggregating the data before doing the calculation.
What I would like to do is have a table in which, depending on the variables used, it weights each row then provides the total
So for example, if I wanted to see September Revenue Per Unit, it should calculate the revenue per unit at each row level, then weight it so I can get the weighted Revenue per unit sold at the monthly level factoring in the Revenue per Unit sold at each of the different levels. I'd like to be able to do this at all levels of granularity to get the accurate Revenue per Unit whether it be by Channel, By Region, by Vendor, or a mix of those.
Please see the screenshot for an idea of how this will be looked at, and the Grand Totals displaying the Rev/Unit of the different types of sales. Please let me know if I was unclear or need to further explain what I'm trying to accomplish.