Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
Unfortunately, that doesn't work The first formula gives me a very high number and the second formula returns an error
Let's talk about first formula first, What number you are expecting?
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?