
1. Re: Percentile  Recreating an excel operation in Tableau
Scott Hoffman Feb 13, 2019 1:50 PM (in response to Shyam Sundar Ranganathan)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

Percentile.twbx 41.9 KB


2. Re: Percentile  Recreating an excel operation in Tableau
Don Wise Feb 13, 2019 1:57 PM (in response to Shyam Sundar Ranganathan)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

Percentile.twbx 25.1 KB


3. Re: Percentile  Recreating an excel operation in Tableau
Shyam Sundar Ranganathan Feb 13, 2019 2:13 PM (in response to Don Wise)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 'simpleid 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 Feb 13, 2019 2:31 PM (in response to Shyam Sundar Ranganathan)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 Feb 13, 2019 2:36 PM (in response to Don Wise)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 Feb 13, 2019 2:42 PM (in response to Shyam Sundar Ranganathan)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 2030% for evaluating whether a price comes close to 25% and the other calculation range is set from 4555% 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

Percentile(DW)_v10.5.twbx 29.1 KB


7. Re: Percentile  Recreating an excel operation in Tableau
Shyam Sundar Ranganathan Feb 13, 2019 2:48 PM (in response to Don Wise)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 Feb 13, 2019 3:00 PM (in response to Shyam Sundar Ranganathan)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

Price Point.twbx 72.9 KB


9. Re: Percentile  Recreating an excel operation in Tableau
Shyam Sundar Ranganathan Feb 14, 2019 3:42 AM (in response to Jennifer VonHagel)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

Price Point_14.twbx 94.7 KB


10. Re: Percentile  Recreating an excel operation in Tableau
Don Wise Feb 14, 2019 8:13 AM (in response to Shyam Sundar Ranganathan)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 Feb 14, 2019 9:18 AM (in response to Don Wise)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 Feb 14, 2019 9:42 AM (in response to Shyam Sundar Ranganathan)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 Feb 14, 2019 10:01 AM (in response to 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 Feb 14, 2019 2:34 PM (in response to Jennifer VonHagel)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

Price Point_14.twbx 94.5 KB
