Formatting numbers in a pivot table

[Sunil_Kenth]

Hi All,

I have a pivot table where each line for the expression called 'Actual'  need to be formatted differently based on the source data.  For example, one set of data that i am pullng in needs to be formatted in % and another set of data needs to fomatted in currency.  In my source excel spreadsheets i have formatted the data as i want it to appear in QV, and in the number formatting settings in the chart properties I have left the setting as expression default. 

What i am getting is all the data appearing as % which doesnt make sense.

Attached is the application.

[Anonymous]

Hi SKH,

none of your values make sense as a percentage.  which ones are suppose to be percentage?

I think the solution to your problem is with the Expressions.  take Actual for a start. 

You could use a nested IF statemen like this.

IF(KPIName = 'Pick Accuracy',NUM(sum({$<DataType={'Actual'}>}Value),'##.##%')
     ,IF(KPIName = 'Store Delivery On Time',sum({$<DataType={'Actual'}>}Value)
           ,NUM(sum({$<DataType={'Actual'}>}Value),'##.##')
           )
     )

You could make this even better by adding in another field element at time of load where you identify the value as '%', '#' and so on.  these would indicate the output should be formated as a percentage or number. 

Let me know how you go.

Regards,

John.

[Anonymous · Accepted solution]

Hi skh,

     Here i attached your qv file with multiple number format using the expression.

     Hope this will helpful you to understand about the format using the expression.

Regards

Ashok

[Sunil_Kenth]

Thank you both for your responses.  I would've marked them both as correct but there is only the option to mark one response as correct!  As Ashok's had the example attached, i thought this would be helpful for other too and therefore marked this as correct.  John, i like your side of adding in and additional column to hold the unit of measure.

Regards,

Sunil