Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
Need Help
  • JoaoMarcelino's avatar
    JoaoMarcelino
    4 years ago

    Hi Anonymous and Anonymous 

    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: 

     

    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

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

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

    3.3- Right-click -> new calculation item

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

    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:


    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:

    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:

    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)

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

    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's avatar
JoaoMarcelino
Icon for Responsive Resident rankResponsive Resident
4 years ago

Hi Anonymous  
How are you? 🙂

I built a small sample data:

Performed a simples sum  as an example: 

Availability % = SUM('Table'[Values])
This results in something like this:

After that, I've formatted the measure as: 

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 🙂