Sometimes, Power BI just doesn’t work the way we expect it to (or as it should), so this is the first post in a new “What The…?” series to explain what’s going on when it won’t do something it should.
Power BI “glitches” sometimes, and it’s good to know how.
This post is about how Power BI glitches when we try and format a long number so it has “thousand commas” so it’s easier to read.
Thousand commas are also known as a form of digital grouping.
For instance, 100000000 can be shown as:
100,000,000 (the usual format it English speaking nations)
100.000.000 (most of Europe)
100 000 000 (the official international standard)
All of the above methods makes it really easy to read a big number like 100,000,000. Contrast that to trying to quickly count how many zeros are crammed together in “100000000”.
If you’d like to use the same data that I do in the below video (if you want to try this at home), here is the data file you can connect to a new blank report in Power BI Desktop:
Video:
Post:
Let’s start with a blank report that has our data connected (the Excel file linked above the video above). If we look at the Table View of our data, we can see there are 3 simple columns in our data.. two text fields and a numeric Value field.
The first thing we’ll do here is learn how to turn on Thousand Commas.
Let’s make a Data Card visual to display a sum of our Value field.
Let’s click the Card visual icon in the Visualizations Pane and create a card space on our Canvas.
Now let’s drag our Value column to that space.
Power BI applies some default settings when we add a numerical field to a card like this. First, it sums up the numbers in that field, and it decides on a unit. For this one, it’s put a “K” to denote 1000s.
We can go into our visual formatting for the “Callout Value” (the number) for this card and adjust the Display units dropdown to be “none” so we get the actual summed number.
You may notice that there is NO Thousands Comma option in our formatting…
So, if we want to have a Thousands Comma, we can’t add it here. We have to go somewhere else to format our number that way (this isn’t the glitch… this is just an annoying way that Microsoft has made things… putting some formatting options in one place and some in another.
To get Thousands Commas applied to our number, we have to select our Value column in the Data Pane. When we do that the ribbon at the top of Power BI changes to a “Column Tools” Ribbon.
You can see a “Formatting” section in this ribbon. It gives us formatting options like formatting a number as currency, a percentage, or adding thousand commas.
Click this and you’ll see a Thousands Comma show up!
Fantastic.
Ok, so this works.
Let’s try something else. Let’s say we want a Card visual with a count of our values.
We’ll do the same process. Click the Card visual icon in our Visualizations Pane, then drag our Value field to it.
We get what we did before… Power BI sums it up and adds a “K” to denote 1000s.
But we want a count, not a sum, so we can click the little down arrow on the Value field where it’s showing up in the Visualizations Pane and select “Count”.
Great. We have a Count now… but you may be noticing that we do NOT have a Thousands Comma showing up for our Count.
If you click on the Value field in the Data Pane and check the Column Tools ribbon, you can see that the Comma button is still selected, so a comma *should* be showing up in our card.
This is our glitch.
It’s been a glitch since I started using Power BI (in 2018) and I still forget about it sometimes. Microsoft has got to fix this already. It’s been long enough.
Here’s how WE fix it for our visual:
We’re going to go into the visual formatting options and into the Callout Value (our number) options.
If you’re thinking “there’s nothing here about Thousand Commas”, you’re 100% correct.
However, adjusting something in here DOES make our Thousands Commas show up, and it’s completely non-sensical.
If we adjust our Display Units to “None” AND we adjust the “Value decimal places” option to something other than “Auto”, THEN our Thousands Comma shows up.
It makes no sense, but it works.
Display Units = None
Value decimal places = anything other than Auto
Let me know if you have questions about this glitch. It’s fixable, but the solution is a bit arcane.
Take care everyone,
Joe.
Post Tags: Glitches | What The…? | Formatting | See all tags
Contact Joe | TraversData.com | People-Friendly Power BI | LinkedIn
This post was made with the June 2024 version of Power BI Desktop.