Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I need to count for each group (rating) if Value is greater fractile 0.85, then 2. If greater fractile 0.65 then 1.5 and so on.
Also I'm excluding filial that colored yellow.
Im trying to do it by formula in visualisation but its not working proparly, I got zeros in value and 2 in Totals.
if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,
if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,
0
)
)
How can I achieve correct result?
Add the total keyword to fractile, or else it will simply aggregate on dimension value
=if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,
if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,
0
)
)
Add the total keyword to fractile, or else it will simply aggregate on dimension value
=if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,
if(
sum({<[filial] =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial] =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,
0
)
)