Toggling Between Different Units via Slicer?

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:

image

Results!

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

   [Scale]:=

   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]:=

   MAX(Selector[ID])

All that one does is detect what the user selected on the disconnected slicer.

Formulas – the Text Values

[Text Version – Millions]:=

SWITCH(
   [Scale],
   “”, “$ ” & 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]:=

SWITCH(
       [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.

Drawbacks!

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)

18 Responses to Toggling Between Different Units via Slicer?

  1. Matt Allington says:

    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.

    Thanks for the tip.

  2. Chris Gilbert says:

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

  3. Chris Gilbert says:

    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(
    [Scale],
    “”, 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) ? :-)

    • powerpivotpro says:

      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.

    • Derek Rickard says:

      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:

      =Switch(
      [Scale],
      “”,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

      :-)

      • Derek Rickard says:

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

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

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

  4. Tony says:

    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.

    • powerpivotpro says:

      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. Ken Puls says:

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

    • powerpivotpro says:

      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. Don Tyrrell says:

    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.

  7. powerpivotpro says:

    Awesome, not ONE comment on the limerick. Bunch of savages in this town ;)

  8. Color me confused: Total Sales is defined as SUM(Table1[Amount Sold]). How is it that this SUM allows us to come up with a formatted version for each of the items in Table1 when used in a pivot table?

    • Avichal Singh says:

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

  9. Nicky says:

    Rob,

    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). :)

    Thanks again!

  10. Lucas Wetzel says:

    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. :P

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

Leave a Comment or Question