My measure produces text OR a number. I want to remove decimals from the number,but remain sortable.

[TM_Visual]

In summary: My measure produces either text or an integer. The integer has decimal places.

I want the number to display 0 decimal places. If I change the number to text, I cannot sort it correctly.

I cannot change the Data Type of the measure to a number, as the measure can contain a text string.

Background:

Based on my data, I have created a for a report that normally would look like:

Year     Number
_______________
2014     21
2015     9
2016     2
2017     15

(please excuse the formatting)

I want to hide the exact number of students if there are fewer than 10 of them, but still include them in the data, rather than simply having them disappear.

I would like it to display:

Year     Number
_______________
2014     21
2015     <10
2016     <10
2017     15

I came up with a partial solution last night:

Number of offers = IF([Number]>9,
[Number],"<10")

Unfortunately this presents a new problem: the number is formatted to include decimal places:

Year     Number
________________
2014     21.00
2015     <10
2016     <10
2017     15.00

As I will never have half a student, this is not acceptable. 

If I try to use 

Number of offers = IF([Number]
>9,
FORMAT([Number],""),"<10")

, the table appears to be correct, but because the numbers are formatted as text, they cannot be sorted accurately. For example, they appear as:

Animal   Number
________________
Fly       1001
Dog       109
Cat       1547
Rat       191

I have tried a few methods including VALUE, FIXED and various FORMAT, but I can't get what I want!

In summary: I want to format the numbers as a number, but without two decimal places.

I cannot change the Data Type to number, as the measure can contain a text string.

Any help appeciated.

[MarkS]

Hi,

I think that you can use the formula that you came up with for the measure (it will be Text).  To make it sort as you want you can set the Sort by Column to be the original [Number] Column.

To do this select your measure in the fields list and then on the Modeling Tab select Sort by Column and select the Number Field.

[TM_Visual]

Sadly this won't work, as I am working with a COUNT and therefore can't sort on the column itself.

Please forgive me, as I simplified a little on my first post: 

-- My starting code:
Number of offers = IF(COUNT([NumberOfStudents])
>9,
COUNT[NumberOfStudents],"<10")



-- My first proposed solution:
Number of offers = IF(COUNT([NumberOfStudents])
>9,
FORMAT(COUNT[NumberOfStudents],""),"<10")

After further testing, it appears that:

• If you have a measure that produces a result that is all numbers, the formatting defaults to a typical value (e.g. whole numbers)
• If you have a measure that produes only text,  it is set to text, and sorts alphabetically.
• If you have a measure that produces either text or numbers, it shows the text correctly, and the numbers with two decimal places. It can sort correctly on this.
• If you use the FORMAT field to try to change the numerical result, it then views that as text, and tris to sort the numbers alphabetically (e.g. sorting 1,000, then 190, then 2000, then 300.)
• If you use the FORMAT field to change the text result to a number with additional formatting, e.g.   FORMAT(10,"\<##")    , then it will still view it as text, and will show any numbers to two decimal places.

Therefore I need either:

• A way to format the numbers to remove the decimal places without causing Power BI to view them as text-like.
• A way for the table to sort correctly, even if all number values have been formatted.
• A new way of adding the text string
• A new way of displaying a text string when numbers fall below a certain value.

[foyiq]

One question I have is why you're using "" in FORMAT fn, instead of 0. I'm not very much into FORMAT fn, but as far as I knew, the latter is the formatting for a whole number w/o decimals. Maybe then you will have the values to be sorted numerically, not alphabetically

[TM_Visual]

Using 0 in the FORMAT still results in the number being formatted as text, ruining the sort 

I've also tried formatting the <10 as a number, adding the '<' using the FORMAT fn. No luck.

Using VALUE presents it as a number; but with the two decimal places that I want to lose.

Thanks for your suggestions so far! I think that this is a bug/missing feature in DAX, or a problem with the way that Power BI visualisations deal with numbers: not allowing the formatting of numbers when they can appear in a field appear alongside text.

Still, I feel that there must be some way around it. 

[v-qiuyu-msft]

Hi @TM_Visual,

Please check which kind of format did you set for the Number column, make sure it's Whole Number instead of Decimal number. Also please update the Power BI desktop to the latest version 2.47.4766.801 64-bit (June 2017).

If issue persists, please share .pbix file with us if possible.

Best Regards,
Qiuyun Yu

[TM_Visual]

Hi, my Desktop is up to date.

Because I have a measure that includes text, I cannot list it as a number.

An example is pictured.

Table 1 is a simple count of the data.

Table 2 is a measure that replaces low values with text. There are unwelcome two decimal places on the numbers. The Measure is 

Number of animals = IF(COUNT(Table01[Animals])>10,COUNT(Table01[Animals]),"fewer than 10")

Table 3 is a measure that formats the numbers. Now, the table will not sort correctyy: It places 1084 as lower than 90. That measure is:

Number of animals as text = IF(COUNT(Table01[Animals])>10,FORMAT(COUNT(Table01[Animals]),""),"fewer than 10")

 I used a very simple, one-column table with a list of animals to create this example. Please advise me jhow I can share it with you if desired.

[v-qiuyu-msft]

Hi @TM_Visual,

I have reported this issue internally, CRI 40800356. Will update here once I get information.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @TM_Visual,

I got information below internally:

"Since the DAX expression returns either a number or a text, the UI currently doesn't give users an option to format the numbers for such measures.

We currently do not support variant measure formatting. Log this request on the Power BI Ideas site."

For your requirement, you can post a request in Ideas forum.

Best Regards,
Qiuyun Yu

[TM_Visual]

As noted in my community post here , there is an issue where the 'table' and 'table preview' visualisations  (and possibly others) will automatically display all numbers to two decimal places if there is a mixture of numbers and text displayed in that column.

This applies even when:

* There are no decimal places worth noting: e.g. all integers are 500.00 / 34.00 / 1.00

* The numbers are formatted as numbers using the FORMAT function

* The 'text' part is a number that has been FORMAT'ed as text or as a number (e.g. FORMAT fn "General number")

If all of the numbers are formatted using the FORMAT function or others, the decimal points disappear. However, the values then left-align (by default) as text, and will not sort as proper numbers: e.g. they will order 1001, 109, 1547, 191, 2000.

I was using this table with a COUNT measure, so I cannot use the 'Sort by column' to sort, and I want the sort order to be easily changed by users.

My measure produces text OR a number. I want to remove decimals from the number,but remain sortable.

As an example, the following produce a result with the numbers with two unnecessary decimal places, eg. 524.00. These do sort correctly.

'Basic formula:
Number of applicants =IF(COUNT('ApplicationsQuery1'[Applicants])>10,COUNT('ApplicationsQuery1'[Applicants]),"<10")

'Specify as VALUE:
Number of applicants = IF(COUNT('ApplicationsQuery1'[Applicants])>10,VALUE(COUNT('ApplicationsQuery1'[Applicants])),"<10")

'FORMAT fn "Fixed" :
Number of applicants = IF(COUNT('ApplicationsQuery1'[Applicants])>10,FORMAT(COUNT('ApplicationsQuery1'[Applicants]),"Fixed"),"<10")

FORMAT fn "standard" : 
Number of applicants = IF(COUNT('ApplicationsQuery1'[Applicants])>10,FORMAT(COUNT('ApplicationsQuery1'[Applicants]),"standard"),"<10")

Number of applicants = IF(
 COUNT('ApplicationsQuery1'[Applicants])>10,COUNT('ApplicationsQuery1'[Applicants]),FORMAT(10,"\<#,##"))

The following produce a result with whole numbers, but they only sort as text:

'FORMAT fn "" :
Number of applicants = IF(COUNT('ApplicationsQuery1'[Applicants])>10,FORMAT(COUNT('ApplicationsQuery1'[Applicants]),""),"<10")

'FORMAT "0" :
Number of applicants = IF(COUNT('ApplicationsQuery1'[Applicants])>10,FORMAT(COUNT('ApplicationsQuery1'[Applicants]),0),"<10")

In another issue I will raise a suggestion that we can automatically choose to have values below a specified amount display as '<10' or '>9000', but that's a separate issue.

[TM_Visual]

To offer an example, please see the image in the Community post here: http://community.powerbi.com/t5/Desktop/My-measure-produces-text-OR-a-number-I-want-to-remove-decima...

Table 1 is a simple count of the data.

Table 2 is a measure that replaces low values with text. There are unwelcome two decimal places on the numbers. The Measure is 

Number of animals = IF(COUNT(Table01[Animals])>10,COUNT(Table01[Animals]),"fewer than 10")

Table 3 is a measure that formats the numbers. Now, the table will not sort correctyy: It places 1084 as lower than 90. That measure is:

Number of animals as text = IF(COUNT(Table01[Animals])>10,FORMAT(COUNT(Table01[Animals]),""),"fewer than 10")

 I used a very simple, one-column table with a list of animals to create this example. 

[foyiq]

I think you can use the ROUND function in your measure instead of FORMAT here.

[TM_Visual]

Hi @foyiq, thanks for the try, but it still shows the decimal (tried with any number specified value in the second part of the ROUND formula)