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.

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.

Download the Workbook!

Share this:

This entry was posted on Tuesday, July 29th, 2014 at 1:40 am and is filed under Creative Visualizations, DAX, Quick Tips & Tricks. You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.

18 Responses to Toggling Between Different Units via Slicer?

He Rob, nice post. As I was reading it this morning at a “jet-laggy” 5am, it occurred to me that “format” accepts currency symbols as inputs to the format pattern. So in any of the formulae you have posted above, you can replace (for example)

“$ ” & FORMAT([Total Sales] / 1000, “#,##0.0″) & ” K”,

with

FORMAT([Total Sales] / 1000, “$#,##0.0″) & ” K”,

and this will give you the same result (except the currency symbol is hard justified to against the first digit.

Rob, I like it! Always need a way to make PowerPivot more appealing to the financial types.

One thing that I keep getting stuck/caught on, is your use of the “-” (hyphen) in your Measure names. Because of the heavy formula emphasis in PowerPivot work, I’m always trying to read that as a subtraction, then after about five minutes I come to my senses and realize, “Oh, that’s just a dash – as in something added on!”

Can I suggest maybe using underscores? I use them in a lot of my other programming for variable_names, and it seems to avoid that confusion, especially at the end of a long day.

You can insert a space just after the currency symbol, if you like that.
Also, you can add the K or M, or whatever text you want as long as you put an escape “\” character in front of the first character.

Underscores seem so nerdy to me. It’s hard for me to sign on for them. Plus – that whole SHIFT key thing! For years I wrote all of my emails in strictly lowercase, because I abhorred the wasted effort of SHIFT. Yes, my laziness ran deep and there is hangover from that era.

No need to divide by anything. The unfortunate part, as Rob noted, is that you can’t plot, sort or do anything else “number-y” (not a word) to the pivot. Because PowerPivot returns a text and not an actual number. So my contribution provides nothing to the result… or does it?

In traditional Excel, applying this formatting (e.g. “$#,###,.0 \K”) to a cell doesn’t change the value in the cell, only how it’s displayed. Break out the old conditional formatting tricks and apply this method to your original measure in the pivot table (not PP). That is, create a rule for anything that is not in between -1,000,000 and 1,000,000 to be formatted as “$#,###,,.0\M”. Then create a rule for anything not in between -1,000 to 1,000 to be “$#,###,.0\K”… ensure you have the rules in the correct order and you will have the formatting you want, with the functionality of the original numbers

With regards to the conditional formatting, you can still fake it, so long as you’re using icon sets. Just set up a column to the right that uses real values, and then set your CF rule to show Icons Only. Won’t help for data bars, but will allow you to put up/down arrows or checkmarks on the right side of your “numbers”.

I would go at this request differently, using a cubevalue formula that captures the unit slicer choice that drives conditional formatting formula solutions for the units. This allows the resulting output to still be numbers but formatted to meet the desired output. I will forward a spreadsheet example showing this alternative solution.

Thanks for this. Absolutely awesome work! It’s stuff like this that causes Powerpivotpro.com to be my #1 bookmarked site for all things BI tools related. The amount of effort (and the ideas I’ve come up with) this site has saved me simply can’t be measure (tho I’m tempted to try with powerpivot).

Got another way without involving a TEXT measure by using Conditional Formatting itself. Pretty much the same INITIAL approach you (Rob) took but this time I expanded the options adding the Thousands and Millions criteria (I personally think including values from multiple scales in the same column is just a nightmare waiting to happen).

1) Create a disconnect slicer table for your multiple scales.

I used Full Amount = 0, Thousands =1, Millions = 2.

2) Create a measure to catch that value.

SelectedScale: =Min(ScaleTable[Value])

3a) Place a version the the following CUBEVALUE formula on your sheet to

3b) For bonus points make it a named range so you don’t have to memorize WHERE it is and easier readability.

4) Create a conditional format catch for each value, see example below. Note “Selected” is my named range for the CUBEVALUE. Formatted below is the Formula to enter followed by “:” then the custom Format option.

Couple things with this.
1) Yes you have to create a separate rule for each condition but it is still a text field. You are not editing the number itself, just changing how it is displayed (so you can still graph it fine).
2) With the Custom Format codes about you will only get a rounding to the nearest value. My “Custom Format” skills are rather poor but I’m sure someone out there can come up with a refinement.
3) If you wanted to use the “K/k”, “M/m”, Etc options I’m pretty sure Custom Formatting should support that too.

This was actually what I THOUGHT you were going to go this route at first because I learned this trick from you.

Great to see some text measure tricks though. Always nice to have more than one way to solve the problem.

And btw Rob, I was actually going to still your limerick and use it for my e-mail signiture (still giving you credit of course) but ultimately I decided no one else in my company is a big enough Excel / PowerPivot nerd to get it.