# 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:

• Hi 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 LOT of 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:

• c1: if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 0,5 then 1 else if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 0,8 then 2 else if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 1 then 3 else 0
• c2: if sum(c1, 0, m1) = 1 then sum(0, -1, 0, 0) + 1 else 0
• c3: if sum(c1, 0, m1) = 1 then sum(d1, 0, m1) else 0
• c4: if sum(c1, 0, m1) = 2 then sum(0, -1, 0, 0) + 1 else 0
• c5: if sum(c1, 0, m1) = 2 then sum(d1, 0, m1) else 0
• c6: if sum(c1, 0, m1) = 3 then sum(0, -1, 0, 0) + 1 else 0
• c7: if sum(c1, 0, m1) = 3 then sum(d1, 0, m1) else 0
• c8: sum(c3, all, m1) / max(c2, all, m1)
• c9: sum(c5, all, m1) / max(c4, all, m1)
• c10: sum(c7, all, m1) / max(c6, all, m1)
• c11: if sum(c1, 0, m1) = 1 then sum(c8, 0, m1) else if sum(c1, 0, m1) = 2 then sum(c9, 0, m1) else if sum(c1, 0, m1) = 3 then sum(c10, 0, m1) else 0
• c12: if sum(c1, 0, m1) = 1 then 0 else if sum(c1, 0, m1) = 2 then sum(c11, 0, m1) - sum(c8, 0, m1) else if sum(c1, 0, m1) = 3 then sum(c11, 0, m1) - sum(c9, 0, m1) else 0

Note: If you want your groups to exceed the 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:

• c1: if sum(d1, d1:-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 0

BR / Ole

• Hi 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