Something like the following should do the trick.
The one cuationary tale with this is that it is an approximation. I am not calculating the values at exactly 20 or 80 %. I am calculating the values are the last point before that threshold is crossed.
I hope this helps,
Pareto Example.twbx.zip 1.4 MB
Brilliant! Thanks Dan.
Nice solution - I had looked to rounding the %, but that could result in more than one result...
Nice work, Dan! I couldn't figure out any easy way around the issue you noted...If I was working with a sparser data set (which is mostly what I work with) I'd probably pad out the data with a couple of extra rows to make the marks for the circles, so as to be able to accurately position the circles no matter where the customers are.
You could also interpolate between the points - it wouldn't give you a more accurate position on the chart for the mark, but the % value should be better:
This is the equation for the 80% line:
IF [Sales %]>.8 and lookup([Sales %],-1)<=.8 THEN "80% of Sales come from " + left(str(
(([Sales %]-0.8)*([Customer %]-lookup([Customer %],-1)))/([Sales %]-lookup([Sales %],-1))
),5) + "% of Customers" END
Just a linear interpolation, so this method will always overestimate the actual value if there is any curve to the pareto.