3 Replies Latest reply on Aug 11, 2016 1:38 AM by Cyrille Thybert

# Calculating Segment with Weighted average price

Hi everyone,

I have issue to solve a calculation (certainly using LOD can solve this).

I want to calculate segment through Weighted Average Price (WAP), let me explain  :

My perimeter is simple, I have different kind of product sold over a year with different prices and each product can have a evolving price.

Dataset sample :

DateProductPriceSales UnitSales Value
01.04.2016product 1101041040
01.04.2016product 2152583870
01.04.2016product 3202865720
01.04.2016product 425571425
01.05.2016product 1111181298
01.05.2016product 2163104960
01.05.2016product 3213587518
01.05.2016product 426641664
...............

I calculate the WAP (over all product) using this formula :  sum([Sales Value])/sum([Sales Unit])

I want to create price segment :

LOW = below the WAP

HIGH = above the WAP

And recalculate the

WAP of the price segment LOW : sum([Sales Value])/sum([Sales Unit]) where selling prices of products are below the main WAP

WAP of the price segment HIGH : sum([Sales Value])/sum([Sales Unit]) where selling prices of products are above the main WAP

And be able to show in a dashboard : HIGH WAP + MAIN WAP + LOW WAP.

I try using this formula to get WAP HIGH :  if [Price]> ({fixed [Date] : sum([Sales Value])}/{fixed [Date] : sum([Sales Unit])}) then sum([Sales Value])/sum([Sales Unit]) end

But I have calculation aggregation issue. If I replace the sum([Sales Value])/sum([Sales Unit]) by [Sales Value]/[Sales Unit] I don't have calculation issue but the result is wrong.

Do you have any idea how to solve this ?

Please find attached a workbook with the dataset.

Thanks,

C.

• ###### 1. Re: Calculating Segment with Weighted average price

Hi Cyrille,

Please try this definition for WAP HIGH:

{fixed [Date]:

sum(if [Price]> ([WAP - Fixed]) then [Sales Value] else 0 end)

/sum(if [Price]> ([WAP - Fixed]) then [Sales Unit] else 0 end)}

It shows the value you are concerned about at 24.708.

Hope this helps!

1 of 1 people found this helpful
• ###### 2. Re: Calculating Segment with Weighted average price

This formula should work as well.

if SUM([Price]) > SUM({fixed [Date] : sum([Sales Value])/sum([Sales Unit])})

then sum([Sales Value])/sum([Sales Unit])

end

It is slightly modified from what you had originally, just combining the two LOD expressions into one and aggregating the LOD and Price. The aggregation issue was happening before because the LOD expression needed to be aggregated and the [Price] also needs to be aggregated. If one measure/dimension is aggregated in a calculated field, all measures/dimensions must be aggregated. The follow article goes into further detail about resolving aggregation issues:

Resolving "Cannot mix aggregate and non-aggregate arguments" Calculation Error | Tableau Software

Cheers!

Hannah @tableau

1 of 1 people found this helpful
• ###### 3. Re: Calculating Segment with Weighted average price

Thank you both !!!

all working.