Hi Koushik try to use to fixed LOD calculation so that quantity and difference column would not be aggregated before multiplication

hope this will work

Hi Koushik,

Not sure this will work or not? I haven't validated this calculated field.

WINDOW_SUM((SUM(IIF([Category]='Books',[Profit],0))-

SUM(IIF([Category]='Cases',[Profit],0)))*

SUM([Quantity]))

Compute using Table (down)

Mahfooj

Koushik,

Let me know If it works

Whenever you've such requirement where you've do some complex calculation then try to split your calculation in small-small results. Like in your case first try to calculated the Profit for Books and Cases separately and validate on canvas check correct output is coming or not. If you're getting correct then you're on right path

Once done then get the difference. Then after multiply with quantity. Then sum it up using WINDOW(). I alway follow this method and it works.

Mahfooj

Okay

I don't have tableau access right now else I must answer with correct calculation.

Are you able to calculated Profit for Books and Cases separately?

Split the calculations and check till get

What this calculated field is returnning?

[Books]: SUM(IIF([Category]='Books',[Profit],0))

[Cases]: SUM(IIF([Category]='Cases',[Profit],0))

Diff: [Books]-[Cases]

Diff*Qty: [Diff]*sum([Qty]) //I've doubt at Qty

Then window_sum([Diff*Qty])

Or we've to take help from LODs

Do you've any other field on rows? Books/Cases values from on field right?

Can you show me exact format of your original data set? How it looks?