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

    Difference between rows

    Rajendra Cheraku

      Hello Experts,

      Need your help!!!

       

      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 UNITCategory[All Markets][All Products][$]
      MFCOLD CEREALTotal US xAOC Incl ConvMF Mainstream Cold Cereal4312472649
      MFCOLD CEREALTotal US xAOC Incl ConvMF Mainstream Cold Cereal (Test)1428269758
      MFBARSTotal US xAOC Incl ConvMF Mainstream BARS730298568
      MFBARSTotal US xAOC Incl ConvMF Mainstream BARS (Test)1404486316
      MFSALTY SNACKSTotal US xAOC Incl ConvMF Mainstream SALTY SNACKS1311250023
      MFSALTY SNACKSTotal US xAOC Incl ConvMF Mainstream SALTY SNACKS (Test)601698284
        • 1. Re: Difference between rows
          Naveen Agarwal

          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
            Joe Oppelt

            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
              Rajendra Cheraku

              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
                Naveen Agarwal

                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
                  Joe Oppelt

                  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.