skip to Main Content

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.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

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:

  1. 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.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. 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!
  5. 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:



Power Pivot: Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Power Pivot: Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

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.

Download the Workbook!

Grab the Workbook Here (2010 Format)

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 19 Comments
  1. 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”,


    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.

    Thanks for the tip.

  2. 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.

    Keep on doing your great work! It’s starting to catch on.

  3. Oh, another little tweak to the formatting string…

    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.

    For instance,
    Text Version_Millions:=SWITCH(
    “”, FORMAT([Total Sales], “$ 000 \USD”),
    “K”, FORMAT([Total Sales] / 1000, “$ #,##0.0 \K USD”) ,
    “M”, FORMAT([Total Sales] / 1000000, “$ #,##0.0 \M USD”)

    Rob, Do you like how I snuck in that ‘_’ (underscore) ? 🙂

    1. 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.

      But your point is well taken.

    2. Variations on a theme:

      Here’s another tip for formatting: use commas at the end of the number format to take the place of 000. For example, to show 1200 as $1.2K use “$#,###,.0\K”. So the above DAX would look like:

      “”,FORMAT([Total Sales],”$#,###.0 \USD”),
      “K”,FORMAT([Total Sales],”$#,###,.0 \K USD”),
      “M”,FORMAT([Total Sales],”$#,###,,.0 \M USD”)

      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


      1. I was just thinking, with this method the original DAX could look something like this:

        =Format([Total Sales],
        “”,”$#,###.0 \USD”,
        “K”,”$#,###,.0 \K USD”,
        “M”,”$#,###,,.0 \M USD”

        Again, doesn’t have the number functionality, but someone may find this useful.

  4. Nice, I like this. All of my bosses hate the ,000’s. One thing though, be careful with those labels. i could easily see that “M/K” getting misinterpreted (17k read as 17,000,000 instead of 17,000).

    BTW i just got your book and was up til 12:30 experimenting with calc fields! Thanks, I’m dragging this morning.

    1. Any ideas that would be more noticeable than M/K? MM and KK?

      Hey, we all work the vampire hours once the sickness takes hold. I finished the blog post at 2 am for instance 🙂

  5. 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”. 🙂

    1. Yeah one of these years we should demand MS gives us a conditional format type that formats measure A according to measure B’s value, even when measure B is not displayed. Damn that would be HOT.

  6. 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.

    1. To get the text formatted version you would use the [Sales – Flexi Unit] measure as defined in this post; instead of [Total Sales].

  7. Rob,

    Thanks for this. Absolutely awesome work! It’s stuff like this that causes 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). 🙂

    Thanks again!

  8. 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

    =CUBEVALUE(“PowerPivot Data”,”[Measures].[SelectedScale]”,Slicer_Scale)

    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.

    =Selected=0 :_($* #,###,##0.00_)
    =Selected=1 :_($* #,###,_)
    =Selected=2 :_($* #,,_)

    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.

    1. 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.

Leave a Comment or Question