cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LukeWatsonAJG
Helper I
Helper I

Data Labels! Formatting 100.00% as 100%

I have the following graph showing availability as a percentage.

How can I keep the data labels at 2 decimal points except for when the value is 100%? 

i.e if 100.00% data label shows 100% whilst the rest of the data shows as 99.xx% 
Capture.PNG

1 ACCEPTED SOLUTION

Hi @LukeWatsonAJG and @RicoZhou 

Thank you for your reference to that Idea vote (it would avoid many problems like this in the future)!

I was discussing this issue with my Power BI Sensei @MFelix and thanks to his help (and also Bas videos "How to Power BI" - Bas, if you are out there thank you too), this is actually doable 😄 The final result: 

JoaoMarcelino_5-1653474277655.png

 

It implies a few additional tricks, but here it goes:

1- Write the following measure

Availability % = SELECTEDVALUE('Table'[Values])

2- Install Tabular Editor 2.x: GitHub - TabularEditor/TabularEditor: This is the code repository and issue tracker for Tabular Edit...
3- Go to  external tools

JoaoMarcelino_1-1653473835518.png

3.1- After opening it, go to right-click Tables -> New -> Calculation Groups (and call it whatever you like, for example, "Format")

JoaoMarcelino_2-1653473950063.png

3.2- Right-click on "Format" -> Create New -> Calculation Item

JoaoMarcelino_4-1653474129771.png

3.3- Right-click -> new calculation item

JoaoMarcelino_6-1653475919561.png

4- Rename it to, for example, "%" and write selectedmeasure() at the Expression Editor

JoaoMarcelino_7-1653476040025.png

4.1- On Properties choose -> Format  String Expression and write down the following code, were "Availability %" is the name of my measure: 

VAR _normal =
    SELECTEDMEASURE ()
VAR formattingString =
    IF (
        _normal = 100,
        """" & FORMAT ( [Availability %], "0" ),
        """" & FORMAT ( [Availability %], "#.00" )
    )
VAR finalFormat =
    IF ( ISSELECTEDMEASURE ( [Availability %] ), formattingString, "" )
RETURN
    finalformat

4.2- Then, click Save and close Tabular editor:

JoaoMarcelino_9-1653476263982.png


Note: If you want to make sure that everything is set and running, refresh your dataset

 

5- Now if you drag the field "Name" to your report, to a table or slicer, the calculation item "%"  will appear:

JoaoMarcelino_10-1653476614186.png

5.1- Now, create a new measure = 1 (the name doesn't matter) and drag it to your line chart. Please keep in mind that this measure = 1 needs to be the  1st measure as shown below:

JoaoMarcelino_11-1653478206747.png

This need to be done to give our Y-axis context, because it is trying to read a text format measure and it doesn't know exactly what to do 🙂

5.2- Now to "hide" the Measure = 1, you can go to chart formatting -> Lines -> Shape -> stroke width = 0 and Colors -> Measure - > White (you could also call the Measure = 1, just " " - a space)

JoaoMarcelino_12-1653478512806.png

5.3- Click on % and voilá, chart with the label you want

JoaoMarcelino_13-1653478557874.png

This was a hard one 😄
Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂



View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

Hi @LukeWatsonAJG ,

 

As far as I know, we could only show one kind of data format in Power BI. If you select Format as  Percentage and the number of decimal places show for the value as 2, all values will show in xx.xx% format. 

If you use format function to determind value = 1 format as 100% and values <1 format as 00.00%, format function will convert the data type to text. You can get result in table visual, however your line visual will be broken. We need number type value.

Your demand is a good idea, while it is not supported to implement in Power BI currently.

You can vote up this idea for this function:

Option to FORMAT () values as numbers (not strings)

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @LukeWatsonAJG and @RicoZhou 

Thank you for your reference to that Idea vote (it would avoid many problems like this in the future)!

I was discussing this issue with my Power BI Sensei @MFelix and thanks to his help (and also Bas videos "How to Power BI" - Bas, if you are out there thank you too), this is actually doable 😄 The final result: 

JoaoMarcelino_5-1653474277655.png

 

It implies a few additional tricks, but here it goes:

1- Write the following measure

Availability % = SELECTEDVALUE('Table'[Values])

2- Install Tabular Editor 2.x: GitHub - TabularEditor/TabularEditor: This is the code repository and issue tracker for Tabular Edit...
3- Go to  external tools

JoaoMarcelino_1-1653473835518.png

3.1- After opening it, go to right-click Tables -> New -> Calculation Groups (and call it whatever you like, for example, "Format")

JoaoMarcelino_2-1653473950063.png

3.2- Right-click on "Format" -> Create New -> Calculation Item

JoaoMarcelino_4-1653474129771.png

3.3- Right-click -> new calculation item

JoaoMarcelino_6-1653475919561.png

4- Rename it to, for example, "%" and write selectedmeasure() at the Expression Editor

JoaoMarcelino_7-1653476040025.png

4.1- On Properties choose -> Format  String Expression and write down the following code, were "Availability %" is the name of my measure: 

VAR _normal =
    SELECTEDMEASURE ()
VAR formattingString =
    IF (
        _normal = 100,
        """" & FORMAT ( [Availability %], "0" ),
        """" & FORMAT ( [Availability %], "#.00" )
    )
VAR finalFormat =
    IF ( ISSELECTEDMEASURE ( [Availability %] ), formattingString, "" )
RETURN
    finalformat

4.2- Then, click Save and close Tabular editor:

JoaoMarcelino_9-1653476263982.png


Note: If you want to make sure that everything is set and running, refresh your dataset

 

5- Now if you drag the field "Name" to your report, to a table or slicer, the calculation item "%"  will appear:

JoaoMarcelino_10-1653476614186.png

5.1- Now, create a new measure = 1 (the name doesn't matter) and drag it to your line chart. Please keep in mind that this measure = 1 needs to be the  1st measure as shown below:

JoaoMarcelino_11-1653478206747.png

This need to be done to give our Y-axis context, because it is trying to read a text format measure and it doesn't know exactly what to do 🙂

5.2- Now to "hide" the Measure = 1, you can go to chart formatting -> Lines -> Shape -> stroke width = 0 and Colors -> Measure - > White (you could also call the Measure = 1, just " " - a space)

JoaoMarcelino_12-1653478512806.png

5.3- Click on % and voilá, chart with the label you want

JoaoMarcelino_13-1653478557874.png

This was a hard one 😄
Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂



JoaoMarcelino
Resolver II
Resolver II

Hi @LukeWatsonAJG  
How are you? 🙂

I built a small sample data:

JoaoMarcelino_0-1653122649139.png

Performed a simples sum  as an example: 

Availability % = SUM('Table'[Values])
This results in something like this:
JoaoMarcelino_2-1653122734221.png

After that, I've formatted the measure as: 

JoaoMarcelino_3-1653122779129.png

In the end I've created the following measure to force the  "100" and only the 100 to be formated with decimal points:

Availability adjusted % =
var _aval = SELECTEDVALUE( 'Table'[Values])
return
IF (
_aval = 100,
FORMAT ( 100, "0,0" ),
[Availability %]
)

Which resulted in only 100 not having decimal places 🙂
Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors