Is this possible?
Someone at Microsoft asked me this question the other day:
“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number? In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes. See below for an example of the desire.”
Can We Do This in Power Pivot?
My Answer: No, not possible. Wait, maybe. Hmm. OK, yes, mostly.
All of these thoughts flashed before my eyes:
- Power Pivot measures/calc fields must always have a consistent data type. You can’t have a measure return numbers sometimes and text other times, for instance. All “exits” from an IF or a SWITCH must have the same data type.
- Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release. They now support “variant” data type measures.
- But no, Power Pivot still lacks that “variant” measure capability, at least for now.
- Whoa, hold on a second. The desired result above does NOT use different data types! It’s all numbers! So we just need to change the math!
- Oh, ouch, not so fast. The “M” and the “K” – I don’t know how to add those labels in a numerical data type.
So this means… text measures!