Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Exactly what I wanted !
Thank you so much