# Calculating Average for TOP / FLOP and Middle Area of Crosstab

Hi Experts,

I'm just working on a very basic requirement that sound simple, but not easy to solve.

Maybe someone has a great idea :-)

We have a list with Values, for example 20 values with a measure.

Now we want to get the Average of the TOP (X) Percent, The FLOP (X) Percent and

… and this is what makes it tricky:

The Average of the „middle“ Section, means the Values lower the TOP (X)% and higher as the FLOP (X)%.

So I want to receive 3 Measures:

- Average TOP (X) %

- Average Middle (for example Area between TOP 20 and FLOP 30) %

- Average FLOP (X) %

I made an example in Excel:

0

## Comments

Ole DyringHi Marc,

Not the easiest one to implement. Not even sure I understood all your requirements. Anyway, here is my take at it:

To achieve the above, you will need to add a

LOTof intermediate calculations.Here is what the table will look like, before hiding all the intermediate calculations (all added as calculated columns):

The calculations I did:

Note:If you want your groups toexceedthe threshold values (0,5 and 0,8 in above example) before shifting to the next level, you may want to change your c1 calculation to this:-1, m1) / sum(d1, all, m1) <= 0,5 then 1 else if sum(d1, d1:-1, m1) / sum(d1, all, m1) <= 0,8 then 2 else if sum(d1, d1:-1, m1) / sum(d1, all, m1) <= 1 then 3 else 0BR / Ole

Marc HoellerHi Ole,

WOW, thanks a lot for your efforts to help in this requirement.

First, I will analyse your formulas to understand what you did, then I try to adopt it to my solution.

At least, I have to create some KPI's which will also show the averages of the 3 groups, TOP, Middle and FLOP. But step by step!

Again: Thanks a lot for your fast and great work here!

😀👍👍👍😀

BR

Marc

Please sign in to leave a comment.