Issue with multiple number formats in a pivot table

[rosey13478]

I have an unusual issue with number formatting in a pivot table I have created.  Basically I need to be able to show some measures as percentages and some as whole numbers.  To do this I have specified the number formats in the expression as follows:

=if(match([Measure ID],11,226,227,228) num(sum(Measures.Numerator),'#,##0'),

num(Measures.Numerator/Measures.Denominator, '#.#0%'))

Where this doesn't work is if the whole number is a 1 then this displays as 100.00% and conversely if the percentage is 0 then I want it to show as 0.00% but it's showing as 0

Has anyone got a workaround for this?

Thanks

[sunny_talwar]

Would you be able to share you file to take a look at this?

[qv_testing]

Goto Number tab ==> Set Number Format Settings as below

[qv_testing]

Use this format...

This is working for me....Zero's as well showing 0.00%.

  =  #,##0.00%

IF(ID=100, Num(Sum(Amount)/Sum(TOTAL Amount),'#,##0.00%'), Num(Sum(Amount),'#,##0' ))

[rosey13478 · Accepted solution]

None of the straight forward number formatting changes worked, so have found a workaround whereby in the load if the value is zero I change this to 0.00000001 so it forces the percentage calculation but has no effect on how the 0.00% is shown in the table.

Thanks all for your help though