Fractile & Aggr function for Benchmarks

[Anonymous]

Hi,

Using the table below, I would like to compare performance (Sales / FTE) of each country to its respective region's 25% Quartile performance. I need to calculate the following 2 KPIs:

1) Country Sales/ FTE - I am guessing: aggr(sum(Sales)/sum(FTE),Country)

2) Regions Sales/ FTE 25% Quartile - which needs to remain at Region level when one country is selected

Any tips would be much appreciated! Many thanks in advance!

Month	Country	Sales	FTE	Region
5	USA	4,390	13	Americas
4	France	2,013	15	Europe
3	Germany	1,385	7	Europe
5	China	5,997	5	APAC
2	Germany	4,154	7	Europe
5	Canada	5,797	16	Americas
4	USA	8,266	7	Americas
1	Canada	4,70	15	Americas
3	France	8,455	18	Europe
1	Canada	2,877	17	Americas
2	USA	5,901	15	Americas
5	UK	9,053	11	Europe
1	Canada	1,704	4	Americas
5	UK	6,220	19	Europe
2	USA	7,296	12	Americas
2	China	9,159	17	APAC
2	Germany	1,008	9	Europe
5	Canada	3,128	5	Americas
3	China	1,009	8	APAC
1	Germany	6,987	13	Europe
5	Germany	8,414	14	Europe
4	Germany	9,010	16	Europe

[Anil_Babu_Samineni]

If you are talking about KPIs then use

1) Sum(Aggr(sum(Sales)/sum(FTE),Country))

2) Sum(TOTAL <Region> Sales)/Sum({<Country = {"=Sum(FTE) < 0.25"}>}FTE)

[Anonymous]

Unfortunately, that doesn't work The first formula gives me a very high number and the second formula returns an error

[Anil_Babu_Samineni]

Let's talk about first formula first, What number you are expecting?

[sunny_talwar]

1) Country Sales/ FTE - I am guessing: aggr(sum(Sales)/sum(FTE),Country)

2) Regions Sales/ FTE 25% Quartile - which needs to remain at Region level when one country is selected

Do you envision them in a chart or a KPI object? I would think chart because you seem to want the 1st one per country, right? 2nd one seems to be per region, so region and country (maybe?) as dimension?