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!
Ode to text measures!
Numbers currency, decimal, and whole
Have long been our lone pivot goal
But with DAX much has changed
Feeds our text value pangs!
Words fill a critical role.
OMG, I love text measures – enough to write limericks about them, yes. I’ve been doing a LOT of very nifty things with them lately and they now firmly occupy a special place in my heart.
Fake data time!
I quickly made a 5-row table of fake data:
Full Amount (Above), and Millions/Thousands (Below)
Formulas – the Harvestor/Detectors
IF([Total Sales] < 1000,””,
IF([Total Sales] < 1000000, “K”, “M”))
All that does is detect how “big” the original numeric value is. I never intend to use [Scale] on a pivot. It just makes my other formulas easier to read.
[Selected Units ID]:=
All that one does is detect what the user selected on the disconnected slicer.
Formulas – the Text Values
[Text Version – Millions]:=
“”, “$ ” & FORMAT([Total Sales], “000″),
“K”, “$ ” & FORMAT([Total Sales] / 1000, “#,##0.0″) & ” K”,
“M”, “$ ” & FORMAT([Total Sales] / 1000000, “#,##0.0″) & ” M”
That one is pretty straightforward. Give me a text version of each number, depending on [Scale], and add the “K” or the “M” where appropriate.
[Text Version – Full Amount]:=
“$ ” & FORMAT([Total Sales], “#,##0″)
That one is even more straightforward. In fact it does nothing that you can’t do with a normal numerical measure. But I need to convert it to text, because otherwise my final measure ends up returning text sometimes and numbers other times, which is not allowed.
Formulas – the Final “Wrapper”
[Sales – Flexi Unit]:=
[Selected Units ID],
1, [Text Version - Full Amount],
2,[Text Version - Millions]
This is the measure we put on the pivot. It branches to “become” one of the two Text measures above, depending on user selection.
Well, you can’t chart text measures. And you can’t conditionally format them either. Those are pretty big drawbacks.
But hey, if all you wanted was the numbers, this technique is OK.
And more importantly, this post is meant to inspire other cool tricks, and NOT just to solve this one problem. I just KNOW you have some cool ideas for text measures.