4 Replies Latest reply: Aug 17, 2016 6:26 AM by Jop Daalmans

# calculate the average of the first quartile

I want to calculate the average of the first quartile, and use it as a limit for a gauge.

All formula's work seperately but i cannot get the total to work.

This is my formula:

avg(if({<bouwmarkt=>}[Totaal Kosten] < fractile({<bouwmarkt=>}[Totaal Kosten],0.25),{<bouwmarkt=>}[Totaal Kosten]))

i can use

fractile({<bouwmarkt=>}[Totaal Kosten],0.25)

to create a reference line and that works fine

i can make an if statement that only shows value if selected bouwmarkt is below 1st quartile average and that works fine:

if([Totaal Kosten] < fractile({<bouwmarkt=>}[Totaal Kosten],0.25),[Totaal Kosten])

So i want to compare a "bouwmarkt" with the average of the 1st quartile.

Does anybody know what i'm doing wrong?

• ###### Re: calculate the average of the first quartile

What is your dimension on which you want to calculate the fractile?

• ###### Re: calculate the average of the first quartile

The dimension is "bouwmarkt"which means DIY store.

I want to compare the values of the selected bouwmarkt to the first quartile of all "bouwmarkt"

• ###### Re: calculate the average of the first quartile

Avg({<bouwmarkt=>}

Aggr(

If([Totaal Kosten] < Fractile({<bouwmarkt=>} [Totaal Kosten], 0.25), [Totaal Kosten]), bouwmarkt))

• ###### Re: calculate the average of the first quartile

Didn't get it to work with Aggr()

But i found another solution with the magical total() function. I dont know what it exactly does but it works!

Avg({<bouwmarkt>}

total(

if([Totaal kosten] <= fractile({<bouwmarkt=>} total([Totaal kosten]),0.25),[Totaal kosten])))