Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
What is your dimension on which you want to calculate the fractile?
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"
How about this?
Avg({<bouwmarkt=>}
Aggr(
If([Totaal Kosten] < Fractile({<bouwmarkt=>} [Totaal Kosten], 0.25), [Totaal Kosten]), bouwmarkt))
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])))