Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous and @v-rzhou-msft 

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 Editor 2.X (free, open-source version).
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
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 @Anonymous and @v-rzhou-msft 

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 Editor 2.X (free, open-source version).
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
Responsive Resident
Responsive Resident

Hi @Anonymous  
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.