Every now and then, I have to deal with a situation where a normalized dataset makes it difficult to build an appropriate pivot table.
Here’s an example:
This dataset contains metrics information for each Market.
Notice a column that identifies the Measure, and a column that specifies the corresponding Value.
Although this is generally a nicely formatted table, you’ll notice that some of the measures are meant to be Number format while others are meant to be Percentage.
In the database where this dataset originated, the Values field is a ‘Double’ data type, so this works.
The problem is that when you create a pivot table out of this dataset, you can’t assign two different number formats for the Values field. After all, one field – one number format.
So in this example, trying to set the number format for the percentage measures will also change the format for the measure that are supposed to be straight numbers.
My solution was to apply a custom number format that formats any value greater than 1.5 to a number. Any value less than 1.5 will be formatted as a Percent.
Here is the syntax I used: [>=1.5]#,##0;[<1.5]0.0%
The result is that each Measure is now formatted appropriately.
Obviously I got a little lucky with the parameters of the situation here. This wouldn’t work in all scenarios. But this techniques does open some options .
RELATED STUFF
Very clever, sir. It’s no bacon mayo, but it is pretty damn clever.
My first visit to this site and I found the first post to be useful! Great tip. Thanks!
Great idea, but I first came across it on the Enron blog.
I had no idea you could do such a value comparison in a custom number format. I was only taught that you had posivite, negative, zero and label segments in number formats.
I don’t know if I will ever have a situation to use it, but still a great tip that could be priceless.
Need to subscribe to this blog, great post. Found it on google.