
1. Re: How to calculate percentage of a specific range over the total?
Michael Hesser Oct 8, 2019 7:12 AM (in response to James Lai)Hi James;
There could be a lot of different factors here are you able to attach a reducedsize sample TWBX so we can understand the basics (data, joins, table details) ?
Your initial equation looks okay what errors are you getting when you try to complete it? Is Tableau saying you can't combine those data types in an expression? Are the resultant values incorrect?
I'd like to see the expression where you are generating the color... you should be able to use a similar equation to count the number of records within those bins less than (or greater than) your target amounts.
Dividing these numbers by the total number of records should yield a percent of total, which I think is what you're after. Let me know if this is not what you want!
Maybe you could also help me with the phrase "I am still unable to compute the idea calculated field" are you just saying you can't get the equation right, or that you are expecting Tableau to do a little number crunching and perhaps return a number to you? (I've done this before using a percential calculation where I want Tableau to give me the optimal range or results where at least 90% of files pass).
I realize this is a little confusing, but if you could get us a boileddown sample (it can be super simple!), that will help a great deal!
Thanks, and Happy Vizzing!

2. Re: How to calculate percentage of a specific range over the total?
James Lai Oct 8, 2019 9:12 PM (in response to Michael Hesser)Hi Michael,
Thank you so much for your reply!
I have just updated my post and attached my twbx file.
The calculated field I have created for the color on my histogram is "Limit with duo data source".
Yes indeed. What I am after is the yield percentage you have mentioned.
So far, I can only understand that i have to make use of this IF statement, IF AVG([Nearest 3])< ATTR([lower limit]) THEN ...... but the big problem is I do not know how to total up the number of records within this boundary since there is no countif function available in Tableau.
Appreciate your kind assistance and inputs.
Best regards,
James

3. Re: How to calculate percentage of a specific range over the total?
Michael Hesser Oct 9, 2019 1:03 PM (in response to James Lai)Hi James;
Let me tell you what I've discovered. Spoiler: no resolution ...but what I've unearthed may help others solve your problem.
I started using the KISS process (Keep It Simple, Stupid). In other words, I tried to reduce the issue to the most basic stage and build up from there. It looks like you know your way around Tableau, so this method may be fairly simplistic to you, nevertheless, here's the stages I went through
Displaying individual [Nearest 3] members I just dropped [Nearest 3] on Columns and converted it to a dimension
Display count of [Nearest 3] I dropped [Number of Records] onto Rows and converted my view to BARS. This matched up real well with your Histogram.
Shifted count of instances to % of instances. Rather than using your CNT(Nearest 3) with a quick calc, I used SUM(Number of Records) with the Percent of Total quick calc. This was personal preference.
Determined if I could calculate percent of records under a specific amount (I'm leaving out the connection to your second table right now). I could it worked!
Count Under
COUNT(IIF(([Nearest 3])<.255,1,NULL))
//counts the number of elements < .255
Count Under Total Pct
{SUM([Count Under])}/{count([Number of Records])}
//Calculates what percent of all records those meeting the [Count Under] criteria represent
Using this formula (and this basic setup), 7.55% of your records fell under the limit of .255
Determined if replacing the model with [Nearest 3 (bin)] broke it it didn't! I just replaced [Nearest 3] on Columns with [Nearest 3 (bin)].
Determined if I could replace the static ".255" with [Lower Limit] from your second table. Here's where things when bonkers: I couldn't do it Tableau wouldn't allo wme because of linking issues I'm not sure how your two files are connected, but it wouldn't let me aggregate the numbers like I wanted. Perhaps someone else can figure this out?

4. Re: How to calculate percentage of a specific range over the total?
James Lai Oct 9, 2019 7:00 PM (in response to Michael Hesser)Hi Michael,
Greatly appreciate your inputs and taking your time to try resolve this problem!:)
I tried using your equation and replacing it with the parameter "lower limiit" as shown below.
COUNT(IIF(AVG([Nearest 3])<ATTR([Sheet1 (Limit data test)].[Lower Limiit]),1,NULL))
//counts the number of elements < .lower limit
Sadly, it didnt work:( I inserted an ATTR function make it an all aggregate value but doing this does not enable the COUNT function to work since, it has already been aggregated.
The "lower limiit" is a single value as compared to Nearest 3 which consists of many rows of values, not inserting the aggregation will not enable the IF function to work.
Ill keep trying and see if there is any other alternatives.
Anyway thanks for your help:)
Best regards,
James

5. Re: How to calculate percentage of a specific range over the total?
Michael Hesser Oct 10, 2019 6:07 AM (in response to James Lai)James
I believe your BLEND is the problem: you're not telling Tableau how to slice & dice the tables together, and including those fields within the view, so Tableau can't tell which Upper and Lower Limits to apply to the data to calculate them and you're getting unsupported aggregation errors.
Here's how I was able to solve it:
I started with an Underdog Flag:
Underdog Flag
IF AVG([Nearest 3])<=ATTR([Sheet1 (Limit data test)].[Lower Limiit]) THEN 1 ELSE 0 END
I put [Underdog Flag] in Detail, but I don't think this is necessary.
Now I leveraged some WINDOW CALCS:
I don't think I've ever fully matured because I still like breaking things down into little tiny pieces; so I made this guy whose sole purpose is to count all the records on the screen:
Window Count All Records
WINDOW_SUM(count([Nearest 3]))
There were 11,808 records, which appears correct once we've eliminated all the NULLS. I consider this a temporary calculation because I'm using it for a gutcheck to make sure the values are in line with expectations.
Last but not least, count how many are Underdogs, and divide by total number of records to get a percent:
Window Count of Underdogs
WINDOW_SUM(iif([ Underdog Flag]=1,count([Nearest 3]),NULL) ) /
WINDOW_SUM(count([Nearest 3]))
//We could substitute the last line with [Window Count Records] if we wanted to.
Hope this helps?

6. Re: How to calculate percentage of a specific range over the total?
James Lai Oct 10, 2019 11:59 PM (in response to Michael Hesser)Hi Michael,
WOW sir, you are fantastic! It works!
I thought it was impossible.
Thank you so much for your help!
Best regards,
James

7. Re: How to calculate percentage of a specific range over the total?
Michael Hesser Oct 11, 2019 4:24 AM (in response to James Lai)It's no problem at all
There are likely more elegant ways to do this I'm just pleased we found one solution!
Happy Vizzing! Michael