5 Replies Latest reply on Aug 26, 2016 8:32 AM by Joe Oppelt

# Difference between rows

Hello Experts,

I have data like below in excel source. I want to calculate \$ value where (MF Mainstream Cold Cereal (Kellogg) / MF Mainstream Cold Cereal). This for Cold Cereal.

I want same value for Bars and salty snacks.

Could somebody help me in formulas how to handle this.

I tried below formula.

(If [All Prodcts] = 'MF Mainstream Cold Cereal (Kellogg)' then [\$] else 0 end) / (If [All Products] = 'MF MainStream Cold Cereal' then [\$] else 0 end]

This formula works for one category that is cold cereal, but for remaining it is very hard.

Any other alternative is highly apprecaited.....

 BUSINESS UNIT Category [All Markets] [All Products] [\$] MF COLD CEREAL Total US xAOC Incl Conv MF Mainstream Cold Cereal 4312472649 MF COLD CEREAL Total US xAOC Incl Conv MF Mainstream Cold Cereal (Test) 1428269758 MF BARS Total US xAOC Incl Conv MF Mainstream BARS 730298568 MF BARS Total US xAOC Incl Conv MF Mainstream BARS (Test) 1404486316 MF SALTY SNACKS Total US xAOC Incl Conv MF Mainstream SALTY SNACKS 1311250023 MF SALTY SNACKS Total US xAOC Incl Conv MF Mainstream SALTY SNACKS (Test) 601698284
• ###### 1. Re: Difference between rows

I think it would be easiest if you had a separate column for Kellogg brands in each category. Is that possible to implement in your data source?

• ###### 2. Re: Difference between rows

I am assuming you have more cold cereals than just Kellogg, and that you want to do each manufacturer divided by the total of all product rows in that category.

Take out the roll-up row.  That's messing you up.

Add in a column for manufacturer.  Then tableau can do each manufacturer divided by the sum of all manufacturers for you.

If I am misunderstanding what you are looking to do, it will help to post a packaged workbook with sample data so we can see what you have and work with a concrete example.

• ###### 3. Re: Difference between rows

Thanks Naveen and Joe. Thanks for your time.

Changing the data source is bit difficult for me. Because we have around 50k rows....and 25 measures (\$).....

I think separating the column is my last option. I have to see....

Here in sample data I have given only category but I have customers and Brand columns also, where user have hard coded formulas in excel and I have to implement this in tableau.

For category, customer and brand I Need to do separate columns......

Any how thanks agian for your valuelable time...:)

• ###### 4. Re: Difference between rows

If you have the Brand column, it may be possible to sum by brand then divide by the total sum to calculate the ratio you are looking for. Can you post a data extract that shows all columns?

• ###### 5. Re: Difference between rows

I don't see the sample data anywhere.

You might be able to simulate the extra column with calcs.  Seeing the data will help determine that.