11 Replies Latest reply on Aug 12, 2013 12:40 PM by brendan.brink

    Turn that calc into a Dimension?

    brendan.brink

      I have cost information that is different in each city (with each city rolling up to a state), and I was hoping to do a weighted average of that cost information for a particular state, weighted based on each cities sales information. I have created a weight tab % of the cities sales in a state, but having an issues Could if I turn that calc into a Dimension? I just wasn’t sure if there was a way.

        • 1. Re: Turn that calc into a Dimension?
          Shawn Wallwork

          You can turn any Measure into a Dimension by just dragging it from the measure window to the dimension window, but I'm not sure it's going to behave the way you you're thinking it will.

           

          --Shawn

          • 2. Re: Turn that calc into a Dimension?
            brendan.brink

            Hi Shawn- thanks for getting back to me! i have tried dragging it over from the measure window to the dimension window, but it comes up with a grey stop sign, that I can not do it. Is there anther way I might be able to do the above request

            • 3. Re: Turn that calc into a Dimension?
              Shawn Wallwork

              Please read this, especially #1. And I'll take a look. Thanks,

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: Turn that calc into a Dimension?
                Joshua Milligan

                Brendan,

                 

                If your calculation is an aggregate calculation -- e.g. contains something like SUM(Sales) -- then you won't be able to use it as a dimension (as an aggregate it is defined by the dimensions in the view and thus can't be a dimension itself).  I guess the bigger question is "what do you want to do with it once it is a dimension?" or "Why do you want it to be a dimension?" There may be other options to consider. 

                 

                But Shawn is absolutely right -- if you can post a packaged workbook it will be much easier to answer your question with specifics.

                 

                Regards,

                Joshua

                1 of 1 people found this helpful
                • 5. Re: Re: Turn that calc into a Dimension?
                  brendan.brink

                  Hi,

                   

                  Thanks both of you for getting back to me. I posted a sample of my file. Essentially what I am looking to try and do it that I have I have hierarchy information that starts with the sku level information and works up to brand. I have both price and cost  data at the lowest level (sku), but was hoping as I compress higher up the hierarchy that I am able to take a weighted average of the price data (based on the sales), and a weighted average of the cost data (based on the sales).

                   

                  I have been stuck trying to figure this out the last couple of weeks, so any assistance or guidance you could provide would be much appreciated. 

                  • 6. Re: Re: Re: Turn that calc into a Dimension?
                    Jim Wahl

                    Hi Brendan,

                     

                    I'm still not 100% clear on your goal (the weighting is a bit odd to me, and I think I'm missing something), but maybe the attached will get you unstuck:

                     

                    I created a Weight field:

                        SUM(Sales) / TOTAL(SUM(Sales))

                     

                    And then created fields Price (weighted) and Cost (weighted) to return the weighted average.

                         AVG(Price) * [Weight]

                    and

                         AVG(Cost) * [Weight]

                     

                    Weight uses the table calc function TOTAL() which is similar to the grand total function in that it will calculate the enclosed value SUM(Sales) over all dimensions that are in the addressing / compute using field. In your example, the default addressing is Table (across), which means all dimensions will be used, which is what you want (I think).

                     

                    Price (weighted) and Cost (weighted) are also table calcs, because they use the Weight field.

                     

                    Here is an example of the output (I swapped rows/columns). You can see that the price for Becel margarine is 311 ($3.11?), but the weighed average price is 231 since Becel is 74% of all sales (220,314 / 296,261). ...

                    2013-08-01 13-08-15.png

                     

                    Jim

                    • 7. Re: Re: Re: Re: Turn that calc into a Dimension?
                      brendan.brink

                      Hi Jim,

                       

                      Thanks for having a look at that. I went through the formaul's you sent me, and I think its close to what I want, but if I give an example it might clarify it a bit more. When I calclued the weight, I did it along pane (down then across). That gave me the weight of each sku within the group "013B", which is 65% for sku 84112833 and 35% sku 84112834 (based on sales number). I was hoping that when I zoom up to "013B" I could get the the cost to be (10.9*65%) + (11.58*35%) = 11.138, which would be the weighted average of the cost based on the skus sales.  I would wnat the cost at the lowest measure to 10.9 for sku 84112833 and 11.58 for sku 84112834.

                      Capture.PNG.png

                       

                      Any insight on how you think i might be able to accomplish this.

                       

                      Thanks,

                      Brendan

                      • 8. Re: Re: Re: Re: Re: Turn that calc into a Dimension?
                        Jim Wahl

                        Thanks for the clarification on the weighting---makes sense now.

                         

                        To do a "SUMPRODUCT" of the price and weight in your table, you need (surprise) table calculations.

                         

                        This will be easier to see in Tableau V8, because of V8 stacks marks by default, where V7 overlaps them.

                         

                        The below screen shots are from V8, but you can use the same process and get the same result in V7 (also the attached workbook is V7).

                         

                        1. Compute Using SKU

                        Since you want to calculate the weight over all SKUs, compute using (aka addressing) should be set to SKU. This is the same as Pane (down then across) in your table, but I find the direct reference clearer and it'll be necessary once you start changing the layout.

                         

                        Here's the starting point (should be same as your example above):

                        2013-08-02 08-56-24.png

                         

                        2. Move SKU to the level of detail shelf.

                        SKU is necessary for the table calc and, therefore, needs to be in the view. But you don't want it in the visual, so move it to the level of detail shelf.

                         

                        After you do this, you'll see the below (in V8; V7 will print the numbers on top of each other). Now the goal is pretty clear, sum the Cost (weighted) and Price (weighted) values, respectively, to get the weighted avg.

                        2013-08-02 09-00-06.png

                         

                        The requires a basic table calc. I created a new field: Price (weighted avg) =

                             WINDOW_SUM([Price (weighted)])

                         

                        Add this to the view and select compute using SKU. (Tip: When entering the formula above, before saving click on Default Table Calculation in the upper right corner of Calculated Field dialog box and select SKU as the default.)

                         

                        2013-08-02 09-05-24.png

                         

                        3. Show only one value

                        The WINDOW_SUM() calculation is compute for every SKU, which is why you see two values / marks that are identical. Since you only want the one value, wrap the above formulas in a IF FIRST() == 0 THEN .... END statement.

                         

                        Price (weighted avg) =

                          IF FIRST() == 0 THEN

                               WINDOW_SUM([Price (weighted)])

                          END

                         

                         

                        4. Disable mark stacking

                        If you're in V8, from the top menu bar, select Analysis > Stack Marks > Off.  The result should look like this:

                        2013-08-02 09-18-29.png

                         

                        Jim

                        • 9. Re: Re: Re: Re: Re: Turn that calc into a Dimension?
                          brendan.brink

                          Hi Jim,

                           

                          Thank you for looking at the results, as you roll up the hierarchy it was coming exactly how I wanted it. I spent the last couple of weeks playing around with it, but couldn't get it.

                           

                          Just one follow up question, is there any way when you drill down to sku, it can show the original information. For example, on promo group 013B, the average is 11.14, but when I drill down to sku it keeps the promo famines average on one of the sku, but leave the others blank. Is there any way it could show what the cost of that individual sku (in this case 10.90 for 84112833 and 11.58 for 84112834) instead of the average for the promo family (in this example 11.14). The goal is for the end user to see the info at sku level, and then be able to drill up to get the weighted average numbers (which now works).

                           

                          Capture.PNG.png

                           

                          Thanks,

                          Brendan

                          • 10. Re: Re: Re: Re: Re: Re: Turn that calc into a Dimension?
                            Jim Wahl

                            Yeah, table calcs can be a bit difficult to wrap your head around at first. It helped me to set the Compute Using directly (using advanced, if necessary) rather than using Table across / Table down  / Pane .....

                             

                            It also helped me to think of Tableau as more of a database front-end than an Excel spreadsheet or R data frame---the difference being that Tableau generates a SQL query every time the view is opened and only the necessary data (fields in the view) in the necessary format (aggregates, if possible) is brought back.

                             

                            Anyway, to answer your question about including SKU. The best option here may be to have two worksheets and use the technique described here Creating a Sheet Selector for a Dashboard | Tableau Software.

                             

                            Basically you create two worksheets and then swap between them on the dashboard using a parameter such as, Show SKU. If the parameter is set to TRUE, the users see the worksheet that has the data at the SKU level; if false, the rolled-up worksheet is shown. There are a couple of points the knowledge base article doesn't core.

                             

                            1. Filters should apply to both sheets (unfortunately global in V7)

                            When uses switch from one view to the other, you want the filter settings to be equivalent. Tableau V7 makes you use a global filter to do this. V8 allows you to assign filters to specific worksheets.

                             

                            2. On filter cards, show all values.

                            You'll have to play with this a bit. But when you hide a sheet, there is no data and the filter cards by default will not show any values. You can change this behavior by selecting the pull-down arrow on the filter card and selecting "All values in database". I did this for the Brand dimension, but this may not work so well for other dimensions. For example, if the BFPP codes are tied to the Brand, then you may not want to show all the BFPP codes when only one brand is selected.

                             

                            There are a few workarounds. In the attached, I used a "custom value list" filter type (select from the pull-down triangle in the filter box), which I kind of like. Ideally users would right-click on dimensions in the view and select exclude, and this filter will show which values are filtered and make it easy to unfilter them.

                             

                            Another option might be to build the filter list from a secondary data source and use a blend to filter. This is a bit more involved, so if you can make one of the built-options work for you I'd go with that first.

                             

                            Jim

                            • 11. Re: Re: Re: Re: Re: Turn that calc into a Dimension?
                              brendan.brink

                              Hi Jim- Thanks for looking at this again, sorry for the delay on getting back to you, I was just on vacation last week. I will work through the file you sent, along with your suggestions. This is very helpful.

                               

                              Thanks again for all your help!