1 2 Previous Next 17 Replies Latest reply on Feb 15, 2019 2:39 PM by Jennifer VonHagel

    Percentile - Recreating an excel operation in Tableau

    Shyam Sundar Ranganathan

      Hello Team,

       

      I am trying to recreate a scenario in tableau which I did in excel.

       

      In Excel : I have attached an excel file, having 3 columns.

       

      Column A  has list of prices (Sorted in Ascending Order) for which a particular product is sold.

      Column B  has the total sales of product for that particular price point

      Column C  has the % of Sales as running sum(Cumulative sales) for each price point in column A. I have attached the sheet for reference. Now what I would like to find the Price at which 25% of sales happened. Here in the excel we could see the row 13 is highlighted as that is the price,where the % Cumulative sales is closer to 25%. Now I would like to recreate the same in tableau.

       

      In the attached tableau file, we have 2 products and for each product I would like to find the price point, where the %sales (cumulative) is equal or closer to 25%. So the idea is to sort the prices in ascending order for each of the two product and calculate the price at which their corresponding %sales(cumulative) is equal or closer to 25% - Similar to what we performed in the excel sheet.

       

      I would like to have your ideas and suggestion in achieving the result.

       

      Thanks,

      Shyam

        • 1. Re: Percentile - Recreating an excel operation in Tableau
          Scott Hoffman

          Shyam,

           

          You can recreate this using table calculations.  Specifically: RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) and computing down the table.

           

          See attached and let me know if you have any issues/questions.

           

          Thanks,

          Scott

          • 2. Re: Percentile - Recreating an excel operation in Tableau
            Don Wise

            Hello Shyam,

             

            Not sure what you're end result is to look like; but, it sounds like you want to try to find the price point nearest the cumulative 25%.  Below is a screenshot which filters to that for both products and a workbook is also attached. If you need to see all of the data, then just remove the filter.

             

            Hope it helps! Thx, Don

            Screen Shot 2019-02-13 at 1.54.02 PM.png

            • 3. Re: Percentile - Recreating an excel operation in Tableau
              Shyam Sundar Ranganathan

              Hello Don ,

               

              Thanks for your reply,

               

              I would like to have the result in the below way,

               

                          Price at `~25% Sales                  Price at ~50% sales 

              A101         XXX                                           XXX

              A102         XXX                                           XXX

                       

              The above would be the preferred format.Unfortunately, i am not able to open the file you have attached. The error message - Error(198,66): no declaration found for element 'simple-id pops up when I try to open the file. Can you please tell me if its possible to sort the prices for a each products within the calculated fields ?

               

              Thanks,

              Shyam

              • 4. Re: Percentile - Recreating an excel operation in Tableau
                Don Wise

                Hi Shyam,

                I'm on 2019.1 versioning.  It appears that no warning is being given when we are working with workbooks that are in an older version. Prices are sorted within each product, yes. Please let me know on versioning. Thx, Don

                • 5. Re: Percentile - Recreating an excel operation in Tableau
                  Shyam Sundar Ranganathan

                  Hello Don,

                   

                  The version I am using is 10.5 . In the sheet I shared , I have the records in order but in real cases, it may not be the same. We may have many records of different transaction happening at different prices.

                   

                  I would like to see the price for each product against 25% of sales.

                   

                  Thanks

                  Shyam

                   

                  Get Outlook for Android<https://aka.ms/ghei36>

                  • 6. Re: Percentile - Recreating an excel operation in Tableau
                    Don Wise

                    Hi Shyam,

                    This is the best i can do with the amount of data provided. The rest will be up to you. Regardless, it should help you get started at the very least. In order to display the values closest to 25% and/or 50% two new calculation help with that. However, the range is set from 20-30% for evaluating whether a price comes close to 25% and the other calculation range is set from 45-55% for evaluating whether a price comes close to 50%.  If the thresholds are set too close to either 25% or 50%, then you'd potentially have no prices displaying.

                     

                    Again, if you need to see all data by product, then simply remove the filter.  Hope it helps!  10.5 is now attached.  Thx, Don

                    Screen Shot 2019-02-13 at 2.29.37 PM.png

                    • 7. Re: Percentile - Recreating an excel operation in Tableau
                      Shyam Sundar Ranganathan

                      Thank you Don ! I will check it. I had a quick look at the formula, you are specifying the range and if we have multiple values, then all of them are listed, but I want only one value that is closer to 25 or 50%. Is there a way you could think of for this ?

                      However , I really appreciate your help and it helped me get closer to what I am looking.

                       

                      Thanks,

                      Shyam

                       

                      Get Outlook for Android<https://aka.ms/ghei36>

                      • 8. Re: Percentile - Recreating an excel operation in Tableau
                        Jennifer VonHagel

                        Hi Shyam

                         

                        You can use a table calculation to create a sales running % of total, sorted specifically by the price point. With table calculations, they don't work properly unless the fields they are derived from are actually present in the view. So this won't return a single number - price point per product at 25% sales - that is just available to use independently anywhere in your data set. But hopefully you can use what's here.

                         

                        I duplicated the Price column to use it as a dimension rather than measure, as we want to group and sum Sales at each price point.

                         

                        Note that when I use "Prices" as a measure rather than a dimension, I use MAX() rather than SUM().  Consider the case where there are three invoices at Price point of $2.00. We want the SUM(Sales) for these three items, but we do not want the SUM(Price), that would give us $6. Because we have Price as a dimension which is grouping measures, we can use MAX(Prices) as a measure to get the expected amount.

                         

                         

                        Here's the Sales Running % Total

                        RUNNING_SUM(SUM([Sales])) / WINDOW_MAX(RUNNING_SUM(SUM([Sales])))

                         

                        Type the calculation in the calculation pane, then choose Default Tableau calculation. We're going to set the formula up so that it specifically does the running sum in the ascending order of price points.  Notice in the Advanced dialog box, under Sort, we will sort by MAX(Prices) and not SUM(Prices). Because this is addressing the Price Dimension, MAX(Prices) will return the correct value.  Click OK out of all the dialog boxes.

                         

                        Now you can see that we can sort the Price Dimension in the view, and the table calculation continues to calculate correctly (by ascending price point):

                         

                        Ok, now we just need to find the Sales Running % that is closest to 25%, and return that record's price point. I broke this up into 3 Steps so you can see what's happening.

                         

                        Calculation 1: Find the absolute difference between each Sales % Running Total and 25%

                        ABS([Sales Running % of Total] - .25)

                         

                         

                        Calculation 2: We know that the min value in Calculation1 is closest to 25%, so let's identify which Sales % of Total is the min:

                         

                        // True or False: WINDOW_MIN(Calculation1) = Calculation1

                        WINDOW_MIN(ABS([Sales Running % of Total] - .25)) = ABS([Sales Running % of Total] - .25)

                        Calculation3: We'll use the True/False of Calculation2 to identify the Price Point to return:

                        IF WINDOW_MIN(ABS([Sales Running % of Total] - .25)) = ABS([Sales Running % of Total] - .25)

                        THEN MAX([Prices])

                        END

                         

                        Or if you want to see that value on every record, wrap the calculation in "WINDOW_MAX"

                        WINDOW_MAX(

                            IF WINDOW_MIN(ABS([Sales Running % of Total] - .25)) = ABS([Sales Running % of Total] - .25)

                            THEN MAX([Prices])

                            END)

                         

                        Here's another kind of cool thing you can do - Plot the Running % Total against the Price Points, and add a Constant Reference line at 25%.

                         

                        Workbook is attached, I hope this helps.

                         

                        Jennifer

                        1 of 1 people found this helpful
                        • 9. Re: Percentile - Recreating an excel operation in Tableau
                          Shyam Sundar Ranganathan

                          Hello Jennifer,

                           

                          Thank you for the detailed response. The steps were very clear and elaborate and the overall solutions looks very neat.

                           

                          I tested the same with fewer columns in the attached workbook (ViewName - Test) and it results the right value.

                           

                          The idea is to use these calculation provided by you along with other additional columns that I am working on separately.

                           

                          Product    Price @25% Column 2 Column 3

                          A101                7.2           xxxx        xxxx

                          A102                7.5           xxxx        xxxx

                           

                          When I try to make the view as shown above, i am not able to do it. Right now we have several rows(Price Dimension) for each Product as highlighted in the below image but then I would like them not to be present and have only the Price@25% against each product.

                           

                           

                          So when I try to do it, i get the below error ,

                           

                           

                          I would like to have your suggestion on how to have only the Price@25% for each product in single row. Hope my explanation is clear and would like to hear back from you.

                           

                          Attached workbook : Price Point_14

                           

                          Shyam

                          • 10. Re: Percentile - Recreating an excel operation in Tableau
                            Don Wise

                            Hi Shyam,

                            I think Jennifer's solution is much more likely the elegant solution you're looking for.  It's more than likely that her Calculation #2 which is a boolean (True | False) could be used as a further filter to bring the view down a bit more to your liking.  We should probably wait and see what Jennifer comes up with as it is her work that's providing a closer result than my brute force efforts. Thx! Don

                            • 11. Re: Percentile - Recreating an excel operation in Tableau
                              Shyam Sundar Ranganathan

                              Hello Don,

                               

                              Yes, I would like to see Jennifer's solution for it. Nevertheless, I really appreciate your inputs as well .

                              I believe the more we have such discussion, the better we can be at Tableau.

                               

                              Thanks,

                              Shyam

                              • 12. Re: Percentile - Recreating an excel operation in Tableau
                                Jennifer VonHagel

                                Hi Shyam,

                                 

                                My thoughts are the same as Don's...  I would essentially hide all the rows except the one where calculation2 = TRUE.

                                 

                                I've renamed Calculation3 to Price Point @ 25% and Calculation2 to Is Price @ 25%.

                                 

                                Table Calculations are a little bit delicate, in that they need to have the underlying values on which they are working (all the records that make up Running % of Sales rows based on price points) actually present in the view, and whatever fields are in the view and the exact way the view is set up can impact how they work.  (I wish Tableau would allow these calculations to work like analytic functions in Oracle - the end result is held in the calculated field regardless of what is put in the view. I should see if someone has added that as an idea and upvote it).

                                 

                                Anyway, what we can do is allow all the Price Dimension records to remain in the view, but hide them.  But, we'd need more information on what other columns you want to add in to the view to see if we can make them work. If they introduce another level of detail, or they aren't associated with the price point @ 25% that is showing in the view, then some other solution may be needed.

                                 

                                A note about Filters - If we filter a dimension to exclude values, the records for those values are not available in the view for a table calculation to work on.  If we filter based on a table calculation, this has the effect of hiding the excluded records, but they are still available in the view for table calculations to work on.

                                 

                                So here, we can use just the Is Price @ 25% field set to true, and the actual Price Point calculation does not have to be in the view - you can put some other measure in there. So now you have the price dimension showing your price point, which is good if you want to add some other dimension columns after it. But again, those dimensions have to be associated with the Price point showing, and when you put them in the view you may need to update the Is Price @ 25% filter to take into account (or not take into account) a potentially different level of detail.

                                 

                                If the columns you wish to add aren't going to work with the table calculation here, what I'd do is create another sheet having the Product as the first column and whatever other columns you want, and then put the two sheets together on a dashboard, formatted and sorted so they look like one table, with the Product column of the second sheet hidden. I've done that several times when clients want tables of numbers in a format Tableau doesn't support.

                                 

                                Best,

                                Jennifer

                                • 13. Re: Percentile - Recreating an excel operation in Tableau
                                  Jennifer VonHagel

                                  And P.S. I agree with Shyam that more voices and inputs are beneficial for all

                                  • 14. Re: Percentile - Recreating an excel operation in Tableau
                                    Shyam Sundar Ranganathan

                                    Hello Jennifer,

                                     

                                    Thanks again for your detailed view on this ! I completely agree with you, table calculations seems to be delicate.

                                     

                                    I tried to implement the above solution you have proposed, but I am able to see the result only for one Product not the other, not sure where I am making the mistake, I have attached the latest workbook view name - test , kindly have a look or please share your latest workbook.

                                     

                                    Like you mentioned, I think if the additional columns complicates the table then I will go ahead with merging two different views in a dashboard!

                                     

                                    Thanks ,

                                    Shyam

                                    1 2 Previous Next