Conditional Formatting in Expression

[mjt_childrens]

Hi All....

I have a scorecard straight table where MetricId is my dimension. I want to format an expression as integer, percent or currency depending on the metricid. Here is a simplified test case I've been working with:

= if(MetricId=2, num(count(CRMRecType),'###,###,##0'),

If(MetricId=5,num(count(CRMRecType)/100,'#,##0.00%'),

If(MetricId=22,num(sum(OpportunityAmount),'$##,###,###'))))

Number format for this expression is set to "Expression Default".

My calculations are working correctly but the formatting is being ignored. Any one of these format statements by itself works correctly but when I put them together the formatting is ignored. What am I missing?

Thanks for any insight into this issue....

Martha

[#2]

How about using the money() function rather than num()?

[mjt_childrens]

Doesn't change the outcome -- still no formatting.

[johnw]

Looks right to me. Is the format being overridden on the Number tab? I'm guessing not, since you say each one works individually.

[mjt_childrens]

I selected "expression default" on the number tab.

[jonathandienst · Accepted solution]

Martha

I suspect the if statement evaluates the expression and returns the evaluated value as a number, so the formatting is applied before and not after the if expression.

As a work around, you could try wrapping the num() statements with a text() statement. Then it is a text expression being evaluated.

I haven't tested this, so I can't be sure it will work. Give it a try and let us know.

Jonathan

[mjt_childrens]

Jonathan,

Wow, you figured it out. I added the text statement and it works! Thank you so much.

Martha

if

(MetricId=2,text(num(count(CRMRecType),'###,###,##0')),

[suniljain]

In case of line chart . this logic is not working.

[#9]

Hi Sunil, did you ever find a fix for this in the case of a line chart? I am having the same problem. I tried wrapping text() function around the formatting but its not doing anything to change my line chart formatting.

Thanks, Sarah

[pennetzdorfer]

Hey,

I've got a bar chart showing the annual sales deviation per region. Through a button the user can switch between relative (%) and absolute (€) deviation. Through an if statement and the num() function i can format the "Values on Data Points" properly.

Unfortunately this doesn't work for the y-axis scale labels. They aren't formatted at all. Does anyone have an idea why?

Regards,

Florian