Forum Discussion
Data Labels! Formatting 100.00% as 100%
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 measureAvailability % = 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 tools3.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 finalformat4.2- Then, click Save and close Tabular editor:
Note: If you want to make sure that everything is set and running, refresh your dataset5- 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.
Responsive ResidentHi 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
finalformat4.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 🙂