Number and Percentage format in a Straight Table

[#1]

Hi,

I have been trying to figure this out all morning so I hope someone can help me!

I have a straight table and it lists all the KPI's which can be either a number or a percentage. In the table I use the expression below. But there are two number KPI's that it keeps formatting as a percentage, if I select them on their own they show as a number, but I need them to be a number in the main table as well. They are 1's but they keep coming up as 100%. If I don't have '#,##0%' in the expression it shows them as 1.0 which would be fine but I need the percentage sign in for all the other figures, as soon as I do that, it changes these two particular KPI's to 100%!

Hope someone can help, I have run out of ideas.

=
if(Format_KPI = '#' And KPI_Order <> '3.12' And KPI_Order <> '3.18',
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)
,KPI_Actual_Format)
,KPI_Description),

if(Format_KPI='%',
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description),

if(KPI_Order='3.12', //it is a number but an average
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description),

if(KPI_Order='3.18', //it is a number but an average
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description))
)
)
)

[marcus_sommer]

I think it's unnecessarily complicated and the nested if's with aggr inside will consume a lot of ressources. I suggest something like this:

simplyfied: num(aggr(), Format)

If Format isn't already matched to the dimension then:

if(condition, Format1, Format2)

     or maybe

pick(match(), Format1, Format2, Format3 ,...)

Try this:

Num(Aggr(

sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator) /

if(Format_KPI='%' or match(KPI_Order, '3.12', '3.18'),

     sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator), 1)

,KPI_Description),

,KPI_Actual_Format)

If the formatting isn't correct then KPI_Actual_Format isn't correct or unique (is there more then one field-value available then returned a Field call NULL without an aggregation-function).

- Marcus

[anlonghi2]

Hi Katy,

try the below expression, based on Marcus solutions.

If it works fine you have to look at KPI_Actual_Format values to solve your format error.

Best regards

Andrea

Num(
Aggr(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator) / if(Format_KPI='%' or match(KPI_Order, '3.12', '3.18'), sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator), 1)
,KPI_Description),
if( Format_KPI='%','##0,00%','#.##0,00'))

[#4]

Hi, Thank you both for your solutions. But none of the expressions fix the problem I'm afraid.

After more digging, I have found that it seems to be one KPI that is always 100% which is causing the problem. Any other KPI’s that have a number 1, always convert to a 100%. I’ve attached an image (bottom image is where I have excluded the 1.4 KPI which is always 100% and the figures are formatted correctly). Do you know of any way I can prevent this from happening?

Thanks so much for your help and I will definitely change my expression to one of these if it will improve performance.

Kate