14 Replies Latest reply on Nov 26, 2018 11:23 PM by Sander Binda

# Issue with weighted average

I've attached a workbook where I want to compare the average price a customer paid within a week to the average price all customers paid within a week.

As you can see the average price of all customers is 2,78 euro, this is a weighted average.

Now my issue is that my last column does not show the weighted average per customer.

For example customer 2 shows an average of 4,85 but the weighted average should be 4,57.

Because in the end the Grand Total of the last column should show 2,78.

(I've calculated the 2,78 in my original data set but for privacy reasons I extracted a small portion of it and put the average weekprice in as hard date).

(Also in my original data set I miss a key to identify each unique row in the full data).

In general my question is if it is possible to let the last column show the weighted average per customer.

Second question would be if this issue is solvable with a LOD formula.

• ###### 1. Re: Issue with weighted average

The numbers are off because the last column is showing the average price per NettKgOut across all rows. If you view the data for customer 2, notice that there are two rows, each with their own [Total sales in €/NettkgOut] value. Those values are being averaged together to get what is currently in the view.

You can get what you are expecting by making the following change to [Total sales in €/NettkgOut]:

[Total sales in €/NettkgOut] : SUM([Total sales in €])/SUM([NettKgOut])

As for whether you can get the average price across all customers, yes, you can get that using a LOD calculation. Try an EXCLUDE LOD expression that removes the [Customer] dimension.

• ###### 2. Re: Issue with weighted average

Thank you for your fast response,

I've made the adjustment to the [Total sales in €/NettkgOut]: and it worked perfectly.

I've got one more question tough, I want these same results using an LOD calculation rather than the LOD calculation you proposed (It did work and it's a good method to check if my other calculations are right but was not quite looking for this).

• ###### 3. Re: Issue with weighted average

What are you referring to by "these same results"? Could you explain in more detail what you are trying to do?

• ###### 4. Re: Issue with weighted average

As you can see the 2nd last row shows the correct numbers.

The last row I used the LOD formula you proposed.

Now I want my last row to show the exact same values as the row before that.

What LOD formula is needed to get the same values as the previous row?

• ###### 5. Re: Issue with weighted average

The reason I want to get those values with an LOD formula is that while the current LOD is article per week, in the future I want to be able to change the LOD to a customer level and/or year level

• ###### 6. Re: Issue with weighted average

Thanks for clarifying. If you want to be able to change the dimensions in the view and have the calculation stay the same, you need a FIXED level of detail expression, including the dimensions currently in the view. That is

{FIXED [Customer], [Article]: [Total sales in €/NettkgOut]}

• ###### 7. Re: Issue with weighted average

Thanks for responding Stephen, the issue I have with that formula is that the grand total of that column does not represent the right value when that formula is used.

• ###### 8. Re: Issue with weighted average

As best I can tell, the calculation is correct and displays the correct total - € 30.44. Is that not what you were expecting?

• ###### 9. Re: Issue with weighted average

That's the sum, I need the average and if you change it to average it should show the average weighted weekprice, but as you can see the average weighted weekprice is 2,78 and not the 3,80 it shows.

I need to find the weighted average price of whatever date interval I want to use, in the example workbook it's a week but I want to be able to filter also on months and years.

• ###### 10. Re: Issue with weighted average

I don't think you can use a FIXED LOD calculation and still get what you want in the totals. You are forcing Tableau to do a calculation at the Customer - Article level of detail with the LOD calculation, then in the grand total you want it to ignore that level of detail and calculate the average across all customers and articles. That is the default behavior of a non-LOD calculated field.

As for what you should use instead, it depends on how your date intervals are implemented. Can you provide a sample workbook with a functional date interval toggle so that we can better understand how this is supposed to work / why regular calculated fields don't give the correct values?

• ###### 11. Re: Issue with weighted average

Here is an example workbook with 1 article, a dozen customers and 3 weeks of data.

Here i tried different LOD, called Avg Wt, but this one shows the average weighted weekprice of the 3 weeks combined, even if not all 3 weeks are selected. When I apply this one on my original dataset I have the feeling it gives me the average price of all dates combined (its 3 years of data so cant really check it).

• ###### 12. Re: Issue with weighted average

Does adding the [Loading Week] filter to context give you what you want? That will make the filter execute before any FIXED LOD expressions are calculated.

1 of 1 people found this helpful
• ###### 13. Re: Issue with weighted average

After trying different LOD and calculated fields it was this simple, thank you for the enlightment!