10 Replies Latest reply on Jun 30, 2016 7:52 AM by Mark Williams Hutchins

# Totalling a table calculation

Hi All, some help please. I'm still quite new to tableau and I'm struggling with a calculation. I have created the below which is working fine:

IF [order / avg]>=1 AND AVG([sales % of forecast])>=0.15 THEN 1 ELSE 0 END

Order / avg is SUM([EA])/WINDOW_AVG(SUM([EA]), -12, -1)

Sales % of forecast is [EA]/[Forecast EA]

This is displaying a 1 where order avg is equal too or greater than 100% and sales % forecast is equal to or greater than 15% but I want the 1's to total up:

Here you can see on the 24th August this happened 5 times so I want the total to show 5 but it just shows 1. Does anyone know how to fix this? Many thanks.

• ###### 1. Re: Totalling a table calculation

Use window_sum() function outside your If..else..end statement. Give a try.

WINDOW_SUM(IF [order / avg]>=1 AND AVG([sales % of forecast])>=0.15 THEN 1 ELSE 0 END)

Let me know If this help. Else share the sample workbook in .twbx format.

Mahfooj

• ###### 2. Re: Totalling a table calculation

Hi Mahfooj,

That has worked but I wish to take customer out but keep the value. I change the table calculation to calculate the table down wards: So at plant 3630 I can see 29th Feb this happened 4 times.

Can I remove customer so this would just show on the 29th Feb it just shows "4" instead of 1 like below (with customer removed)

Many thanks.

• ###### 3. Re: Totalling a table calculation

Can you share a sample workbook?

• ###### 4. Re: Totalling a table calculation

Hi Mahfooj,

Many thanks for your help. I have recreated with the window sum, it is counting across fine but take out customer and it goes and currently is not counting down. Many thanks

• ###### 5. Re: Totalling a table calculation

Please find the work book attached

• ###### 6. Re: Totalling a table calculation

Hi Mark,

I hope this is what you wanted?

Let me know If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 7. Re: Totalling a table calculation

Hi Mahfooj,

Many thanks for this. That is exactly what I wanted. Thanks. I wanted this by plat and material so do I still have customer in the measure area for this to work. Thanks again and sorry for the delay!

• ###### 8. Re: Totalling a table calculation

I have created this but as you can see the last line has 12 twice rather than once, do you know why this is. ! number would be ideal:

• ###### 9. Re: Totalling a table calculation

I've included plant(forecast) in the viz. Just follow the workbook which I've given. Just try to see the table calculation which have been used in the calculated fields. Let me know If you've any query.

• ###### 10. Re: Totalling a table calculation

Many hanks for this. I need to display by plant then material and not actually show the customer but for it to count by customer, so in the example it would show first the plant, then material and using the above it would just show 2 as this occurred twice. Many thanks.