Make a percentage of values between an average value

[fredericvillemi]

Hello,

I have a list of customers with a different number of invoices.

I would like to calculate a percentage of invoices that are less or more 10% from the average price of invoices

Lets' say I have 

Microsoft    invoice1     1000

Microsoft  invoice2    1200

Microsoft   invoice3   1600

Google invoice10    100

Google invoice20     200

Google invoice30     220

The chart would be

Company / Average / Percentage of -10%+10% aournd average

Microsoft    1266                         number of invoince between 1140 and 1393   =   33%

Google      173   number of invoice between 156 and 190 = 0%

I can't find the way to calculate the third column in a single expression

Thanks

[jensmunnichs · Accepted solution]

I think it would be easier to do the calculations in script, however I managed to make it work in a chart with some help from this post.

The expression:

=Sum(If(Aggr(Value, Company, InvoiceNo) > Aggr(Avg(TOTAL <Company> Value)*0.9, Company, InvoiceNo) and Aggr(Value, Company, InvoiceNo) < Aggr(Avg(TOTAL <Company> Value)*1.1, Company, InvoiceNo), 1, 0))
/
Count(Value)

Have a look at the attached file to see the expression in action.

[fredericvillemi]

Exactly what I wanted !

Thank you so much