Excel Chart - number formatting as decimal only if decimal values are available in source data

[manekankit]

I am having chart based on power pivot where values for all the months are updated based on slicer Selection. (E.g. Profit, Profit%.)

I am currently using this number format "#,##0_);[Red](#,##0)" for chart labels which is available by default in excel. This format hides decimal values and shows numbers in full value on the chart Label.

Profit number is not having decimal in source data as it is rounded, whereas profit% is having decimals in source data.

Now format I mentioned is ok if we want to see profit number (which is more than million) on chart labels (E.g. chart would show 15,000). However when profit% is selected on slicer, I want labels to show numbers in 2 decimal points (E.g. Chart should show 10.12).

Note: Profit values in source data is stored as 10.12 (and not 0.1012).

Above is an example, basically I am trying to report all smaller values in decimal on the labels. I can round big numbers to zero decimal in source data.

Further numbers to be formatted for million comma format and negative numbers to be shown in red within brackets.

Requesting community members to help me with this issue, or suggest if there is any workaround.



I am using office 365 for desktop on Win10.