1 Reply Latest reply on Nov 5, 2013 9:58 AM by Tracy Rodgers

    Can you figure this out?

    Benjamin Maulding

      Hey everyone,

       

      I am having some trouble replicating some information from Excel. I am trying to develop a KPI that was originally set up in Excel. In the Excel file I am looking at how well store are performing. The stores are evaluated using a percentage that is based off of the grand total of sales for that type of store, which can be viewed in cells B17-D17, (Equation # 1.) That is only one portion of the equation however. I need to see how well each item is performing by calculating the percentage of sales for individual items. For example, in the Excel File, row two is 2% Milk. I need to calculate the the number of units in a East store divided by the total number of units in all stores (Equation #2.) Now that I have the two percentages the equation will be working with I need to divide (Equation # 2) / (Equation # 1). The the results need to be multiplied by 100. This process should render the results views in columns F, G, & H. The mock Excel file and Tableau workbook are attached. Please Excuse the NULL values.

       

      Can anyone help me find a way to replicate these results in Tableau?

        • 1. Re: Can you figure this out?
          Tracy Rodgers

          Hi Benjamin,

           

          Sorry for the delay in receiving a response. The best way to accomplish this would probably be to reshape your data. I've reattached the Excel sheet with the data reshaped (refer to Sheet1-Tableau)

           

          Then, a couple of calculated fields can be created.

           

          total(sum(Value)) --this one should be computed by Store

           

          total(sum(Value)) --this one should be computed by Product Name

           

          Then, created a third calculation that divides the second field by the first.

           

          Hope this helps!

           

          -Tracy