DAX to Test for Whole Number

Question

I have a Actuals column like so:

ID | Airport
------------
A  | 98.4
B  | 98.0
C  | 95.3

I'm attempting to format the numbers above into percentages for a front-end report. I have this written in a switch statement - for ease I'll just write the logic as an IF boolean.

example_measure = 
VAR Nums = SELECTEDVALUES(Table[Actuals])
VAR FormatNums = IF(DIVIDE(ROUND(nums,1), nums) = 1,
                              format(nums,"0%"),format(nums,"0.0%")
-
RETURN 
FormatNums 

no matter what I do this always returns a number with a floating point value of 1f

so in my raw data of 98.0 I'm expecting the format to return "98%" rather than "98.0%"

the measures are used on individual cards, and are filtered so they can only ever show one value or blank, meaning they will only ever display one value on their own.

I've toyed with using if(nums = int(nums) which should evaluate to true for 98.0 but it I always get false.

Answer 1

To convert a column/measure into percentage, you can simply divide the column by 100 and then format it as a percentage. Use the following steps:

1. Create a new column/measure: Perc_value = Table[Actuals]/100
2. Then go into the modelling tab, select the created column/measure and format it as a % and limit the number of decimal places to 0

This should give the view you are looking for. Hope this helps.

Edit:

You can use the below formula to achieve the desired result:

Column4 = IF('Table'[Column2]-ROUND('Table'[Column2],0)=0,FORMAT('Table'[Column2]/100,"0%"),FORMAT('Table'[Column2]/100,"0.0%"))

Replace Column2 withyour field and you should be good to go.

Answer 2

There is a simpler way - just use built-in formatting codes:

Formatted Actuals = 
  VAR x = SELECTEDVALUE(Data[Actuals])
  RETURN
  FORMAT(x, "General Number") & "%"

Result:

Built-in style "General Number" handles your situation correctly, you just need to add % symbol to the formatted string. No need to test for whole numbers.