PBI only recognize % as Text type after using Dax: Format ( value, "0.0%) - Help

Used below DAX to format set of numbers, inorder to show percentage as percentage, number as numbers.

Value = if([Value]<-1,[Value],if([Value]<1,FORMAT([Value],"0.0%"),[Value]))

eg: 100,000 will show as 100,000

0.1 will show as 10%.

However, once used the above DAX, the % value cannot be treated as number, been recongized as TEXT only.

PBI will give error message, if want to do a simple subtraction such as 25% - 10% .

tex628
7 years ago
The FORMAT() syntax always converts the value to string. So if your aim is to make calculations it is not possible to use FORMAT().

To my knowledge you will not be able to mix percentage with number formatting while still maintaining numeric format on your column/measure.

tex628
Community Champion
7 years ago
The FORMAT() syntax always converts the value to string. So if your aim is to make calculations it is not possible to use FORMAT().

To my knowledge you will not be able to mix percentage with number formatting while still maintaining numeric format on your column/measure.
- vitorgps
  Regular Visitor
  3 years ago
  Is it possible nowadays?
  - tex628
    Community Champion
    3 years ago
    Hi vitorgps ,
    
    To my knowledge this is still the case.
    
    But you can always have a completly numeric measure in the backgroud that you use for calculations, while displaying the formatted one.
    
    So if you have two formatted measures displaying both numeric and percentage formats:
    Measure 1 (formatted)
    Measure 2 (formatted)
    
    You can then have copies of those, that are completly numeric:
    Measure 3 (numeric)
    Measure 4 (numeric)
    
    You can then calculate a new measure 5, dependant on measure 3 & 4. Then you format measure 5 to match the formatting of 1 & 2.
    
    Hope this helps,
    J
LR_PublicHealth
Regular Visitor
2 years ago
In case anybody stumbles across this in future, you can now select "dynamic" formatting from the taskbar and use an expression to alter formatting between % and # as needed. For example:

Forum Discussion