topic Re: calculate the average of the first quartile in App Development

calculate the average of the first quartile

Anonymous — Sat, 16 Nov 2024 10:27:34 GMT

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?

sunny_talwar — Mon, 15 Aug 2016 15:34:47 GMT

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

Anonymous — Tue, 16 Aug 2016 08:14:03 GMT

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"

sunny_talwar — Tue, 16 Aug 2016 09:17:25 GMT

How about this?

Avg({<bouwmarkt=>}

Aggr(

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

Anonymous — Wed, 17 Aug 2016 10:26:59 GMT

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])))