This may seem trivial, but it did not pop up in my head at first, so might be useful to somebody else.

In sales reports, there are lots of numbers. And if it’s a large company these numbers may be very large. So depending on the visual, the full number may be a bit too much, and having just thousands or millions is more than enough.

«Fear not», you will say, Power BI has this use case on it’s ‘done’ list and you can choose the display units from the format pane. Well. Maybe. But it’s so rigid.  I mean, if you don’t like whatever word or letter it shows for the unit, you’re stuck. You can’t change the display units without a suffix. And if you configure power bi in catalan (who doesn’t? — just kidding, I use the English version), the «abbreviation» for (american) billions is something like

Yes, we say like a «thousand millions», and even «hundreds of thousands of millions». And carry on our lives.

But back to the suffix, with all this length you might better just write the whole number which will be more informative. Also imagine a long list. You’ll be like, «can’t you just say the unit once at the title??» and you would be correct. That’s what you expect when you consume data. Now, how can we do that?

If you come from Excel (like myself) you might be familiar with format strings and the totally awesome things you can do with them. Sometimes this knowledge will be helpful in Power BI (was totally helpful for this post), but sometimes will not, because, guess what, Excel format strings and Power BI format strings are not quite the same. Excel’s are more powerful. Ok, with calc groups you can do more in some settings, but just string vs string, Excel allows you to do more stuff. It allows even for basic conditional color formatting! (I’m not kidding )

But we’re not here to talk about colors. We were talking about display units. In Excel, you can show a number in thousands just modifying the format string to 0. (a zero and a dot — or a zero and a coma depending on your regional settings)

However in Power BI the same magic does not happen. You can type in the format string (don’t be fooled by the dropdown, you can actually type format strings there), but it just does nothing.

UPDATE! apparently it does work! I have an issue (I wonder if shared with anyone else … in any case please vote for me!) that format string changes do are not applied until I make some change to the measure (like an enter or something) and indeed it does work! Shootout to Isidre Bagué that pointed this out to me. So is this post pointless? Well it is not for reasons that I detail now in the following paragraphs: 

Another issue is that even if that did work (which it does) we would have to change the format string of the measure, affecting all visuals where it is used. Or at least all visuals where no calc group is modifying it’s format string of course.

Calculation Groups? Let’s see where this goes. Well, as we just learned we cannot do it with format string alone, so a calc group with only format expression will not help. But with a calc group you can also change the value! And you decide in which visuals it should affect! Isn’t it wonderful?

Yes, we are yet in another use case of calc groups.

In general our Sales Amount measure is fine being a currency, because it’s what it is, after all. We just want to tweak it so that in a table (or a chart or wherever) it shows thousands (or millions, you get the idea) instead of the real number.

if it’s just one measure, it’s pretty straight forward. Our «Number Format» calc group, could have a calc item called «compact format» with a value expression that goes like

IF(
    ISSELECTEDMEASURE([Sales Amount]),
    SELECTEDMEASURE()/1000,
    SELECTEDMEASURE()
)

You could just keep adding any other measures that need this behavior. But with real projects things can get messy as new measures are being created and added to the table so you would be comparing pears and apples. Now as Alberto Ferrari would say, «can we do any better?»

Well, what we want to do is that any measure that looks like an amount is divided by 1000. How do we know? Your best bet might be the format string, and lucky you, this can be queried using SELECTEDMEASUREFORMATSTRING() <- probably the longest dax function name ever.

If we want to check exactly what is returned by this function we can create a calc item «show format strings» where the value expression is just that:

SELECTEDMEASUREFORMATSTRING()

Now we can put a few measures in a table, apply the calculation item and get the idea. Before that, though it might be a good idea to explicitly define the format strings of your measures. First of all is a good practice (I think I read that somewhere) and also it will allow you to better identify your measures. If in Power BI the format drop down menu is «General» you will not get any format string.

For date measures you’ll realize that the dropdown is not so useful as there are no options for dates. Weird, because there are several format options for date columns.

here options for column format

Here for measure

I guess the approach is to set the column format and the measures will take it from there — but still, it would be nice if they added some default date formats. End of rant.

But anyway, both for columns and measures, in the format drop down you type whatever format you like, for example dd-MMM-yyyy (no double quotes needed around it)

Notice all these black slashes, I understand is some sort of escape character, so don’t freak out. That is something that changed not too long ago. One day updating a remote model with ALM toolkit it was like all measures needed update even as I didn’t do any change in them. I did freak out, but it was just the back slashes.

For measures that are whole numbers or decimal numbers be explicit about it and you’ll have more control. In this cases you can just select from the drop down list (but remember you can type your format string if you so desire)

Just remember, if you don’t see anything when applying the «show format strings» calc item, you did not specify the format string. If you run the best practice analyzer will probably tell you as well.

Now that we know what DAX will see, we can build a function to do what we want.

The requirement could be to make a compact table, where amounts are shown in thousands and no decimals (and no suffixes!), while percentages are shown in one decimal instead of two. This last option could be set through the formatting pane, through the field formatting menu, going one field at a time. But since we are setting up a calc group for the purpose, we can wrap that into it and we’ll have a general solution, so that if a new measure is added it will be formatted as it should.

So we go back to our initial  «compact format» calc item and make a more general expression

 

VAR fString = SELECTEDMEASUREFORMATSTRING()
VAR isAmount = CONTAINSSTRING(fString,"$")
                || CONTAINSSTRING(fString,"0.*")
RETURN
    IF(
        isAmount,
        SELECTEDMEASURE()/1000,
        SELECTEDMEASURE() 
    )

UPDATE: since the format string does work to show values as thousands, better leave the value expression just as

SELECTEDMEASURE ()

Here we single out only the amount measures because are the only ones that need to modify their value.

We test it and indeed, it modifies only the measures that we want it to.

 

For Format Expression we can go a bit more overboard if we want to define a «compact form» calc item. For the amounts, we want to get rid of the $ sign (because now it is thousands of dollars) and no decimals, we only want one decimal for percentages, and we want an even shorter date format. (sorry for coloring, daxformatter.com still does not get calc items, will fix when it does)

VAR fString = SELECTEDMEASUREFORMATSTRING()
VAR isAmount = CONTAINSSTRING(fString,"$")
                || CONTAINSSTRING(fString,"0.*")

VAR isPercentage = CONTAINSSTRING(fString,"%")

VAR isDate = CONTAINSSTRING(fString,"d")
                || CONTAINSSTRING(fString,"M")
                || CONTAINSSTRING(fString,"y")
RETURN
    SWITCH(
        TRUE(),
        isAmount,"#,0",
        isPercentage, "0.0%",
        isDate, "d-M-yy",
        fstring
    )

This looks fine, right? well, not so fast. It turnes out that CONTAINSSTRING(fString,»0.*») is also true for the percentage format string, so we need to tell percentages apart first. Like this

VAR fString = SELECTEDMEASUREFORMATSTRING()
VAR isPercentage = CONTAINSSTRING(fString,"%")
VAR isAmount = (CONTAINSSTRING(fString,"$")
                    || CONTAINSSTRING(fString,"0.*"))
                && NOT isPercentage    

VAR isDate = CONTAINSSTRING(fString,"d")
                || CONTAINSSTRING(fString,"M")
                || CONTAINSSTRING(fString,"y")
RETURN
    SWITCH(
        TRUE(),
        isAmount,"#,0",
        isPercentage, "0.0%",
        isDate, "d-M-yy",
        fstring
    )

 

But here is when it gets tricky. When I applied changes to the model, the sales amount would not be shown in thousands…

What the french toast?!

After some testing I did found out something interesting

SELECTEDMEASUREFORMATSTRING() in the value expression returns the format string that results from the Format String Expression.

So if I remove the dollar sign from the format string in the format string expression, I cannot use the dollar sign to determine if a measure is an amount. OMG. What can we do? The whole point was to remove characters from around the number, so it looks like we are out of luck, but we are not, because once again the Zero-with Unichar character will help us out. When removing the dollar sign of the format string, we can add this character, and then from the value expression check for that character. If it’s there it means it is an amount (why would it be there otherwise?). Let’s see if it works.

This is our format string expression now:

VAR fString = SELECTEDMEASUREFORMATSTRING()
VAR isPercentage = CONTAINSSTRING(fString,"%")
VAR isAmount = (CONTAINSSTRING(fString,"$")
                    || CONTAINSSTRING(fString,"0.*"))
                && NOT isPercentage    

VAR isDate = CONTAINSSTRING(fString,"d")
                || CONTAINSSTRING(fString,"M")
                || CONTAINSSTRING(fString,"y")
RETURN
    SWITCH(
        TRUE(),
        isAmount,UNICHAR( 8204 ) & "#,0",
        isPercentage, "0.0%",
        isDate, "d-M-yy",
        fstring
    )

And this the value expression

VAR fString = SELECTEDMEASUREFORMATSTRING()
VAR isPercentage = CONTAINSSTRING(fString,"%")
VAR isAmount = (CONTAINSSTRING(fString,"$")
                    || CONTAINSSTRING(fString,"0.*")
                    || CONTAINSSTRING(fString,UNICHAR( 8204 )))
                && NOT isPercentage    
RETURN
    IF(
        isAmount,
        SELECTEDMEASURE()/1000,
        SELECTEDMEASURE() 
    )

Let’s see if it works!

It does! Now we shall not forget to state the visualization unit somewhere —

Now that’s something that we can use.

We can do all sorts of things, and maybe make a dynamic format that depending on the maximum value shown on the table establishes the display units (as it can change with the filters), but that might make things more confusing. As it is now is something easy to understand and that will avoid writing pointless measures just to show values as thousands without any suffix. Thank you for reading all the way to the end.

UPDATE: This article complicates things a bit because indeed we can just modify the format string to show thousands — but using a calculation group to show a compact table of the data continues to be the best approach. Also we learned that SELECTEDMEASUREFORMATSTRING() returns the format string of the calculation group after it has been modified by the format expression.

So remember, if you are in dot-decimal locale,

• to show thousands use «#,##0, »
• for millions «#,##0,,» and so on. 

 

Update 2023-11-26: If you want to see how to industrialize the easy option through format strings, watch this recording of my presentation at DataBash 2023

Follow the conversation on Twitter and LinkedIn