Question:
Hi guys,
I was wondering if anyone can help with the following problem:
Based off the average sales result per region, show me all regions that have sales above the average.
0REGION Sales (0AMOUNT Key Figure)
Region 1 $50
Region 2 $80
Region 3 $60
Region 4 $80
Region 5 $30
From the above data, we only want to see Regions 2,3 and 4 (as they have an average equal to or above the national average of $60).
Currently, this is a 2 step approach if I am not mistaken. Firstly, I need to determine the average sales, which I do by creating a KF with an average exception aggregation.
However, I am not able to parse this value into a condition. Any ideas on how to do that or has anyone done something similar. Any help would be much appreciated. When I create my condition, it does not allow me to read the average value, I can only create master data variables which is not really good to me.
Confused,
Sharon.
Answer:
I have an idea, but you'll have to play around and make it work. This is it:
1) Create a column RegionCount, and fill it somehow with '1' per region (if you don't have details under Region, this can be just a formula = 1).
2) Create a column SumRegionCount, as a formula SUMCT(RegionCount)
3) Create a column SumRegionSales, as a formula SUMCT(Sales)
4) Create a column SalesAverage, as a formula SumRegionSales/SumRegionCount
5) Create a column BiggerSales, as a formula (Sales >= SalesAverage)*Sales
6) Hide all columns except the last one
7) Set the query to exclude zeroes
Depending on the details you have below Region, and the possibilities of navigation, you'll need to decide how to fill the '1' for each region, and if you need to use SUMCT, SUMGT or SUMRT.
_________________
Leandro,
Tuly Idiots Shadow Supporter
Answer:
Hi lhowlin,
Thanks for your reply. It was really helpful in gaining what I want.
I've had a rough play around with the RegionCode counting of average but it's not giving what I exactly want. I've applied a temporary solution by hardcoding in the query the number of regions so the average can get calculated. I'll try the other SUM functions and hope they can sum up the number of regions.
Thanks a lot once again.....
Cheers,
Sharon