Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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%
Solved! Go to 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:
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 🙂
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:
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 🙂
Hi @Anonymous
How are you? 🙂
I built a small sample data:
Performed a simples sum as an example:
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:
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 🙂
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |