cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DarthTim
Frequent Visitor

Display of various number formats in text field (thousand separator, bytes to right of decimal)

I'm displaying values into a Matrix where the values can be either whole numbers, currency, percent or decimal. Everything is working correctly. However, I would like to make three improvements to how the various data types are displayed and am struggling to get it right.


For my current process, I'm doing the following:

 

STEPDESCRIPTION
1

Taking all the values (the various numbers whether they are a percent, currency, decimal or number) and placing them into a single column that is defined as type 'DECIMAL NUMBER'.

 

2

Creating a new column of type 'TEXT' and populating it as follows. [Value] is the value mentioned in step 1.

 

= IF('Data Append'[Type]="Currency",FORMAT('Data Append'[Value],"Currency"),
(IF('Data Append'[Type]="Percent",FORMAT('Data Append'[Value],"0.0%"),
(IF('Data Append'[Type]="Deci",FORMAT('Data Append'[Value],"0.00"), FORMAT('Data Append'[Value],0))))))

 

 

At this point, the data is formatted as expected:

  • Example of Currency: $20,000,000.00
  • Example of Number: 20000
  • Example of Decimal: .09
  • Example of Percent: 50%

 

For my request, I'm trying to:

 

RequestDescription
1For the whole numbers, I would like to add thousand separators
(Example: from '20000' to '20,000')
2For the currency, I would like to remove the two digits after the decimal
(Example: from '$20,000,000.00' to '$20,000,000')
3For the currency, I would like to see if I can display this in thousands so it is not as large a number, and include a thousands separator.
(Example: from '$20,000,000.00' to '$20,000k')

 


For request 1 above, I have no idea on how to accomplish that other than logic to break each value down based on length and insert in commas. I'm really trying to avoid, so I thought I would ask if anyone knew of a magic solution (those exist, right?)

 

For request 2 above, I havent been able to find an answer to this either.

 

For request 3 above, to round it to thousands I was able to try the following. This works, but still leaves me with the currency not having the thousands separator.

 

ValueFrmtK =
VAR
CurrResult = IF('Data Append'[Type]="Currency",ROUND('Data Append'[Value] / 1000, 0),1)
RETURN

SWITCH('Data Append'[Type],
"Percent", FORMAT('Data Append'[Value],"0.0%"),
"Decimal", FORMAT('Data Append'[Value],"0.00"),
"Number", FORMAT('Data Append'[Value],0),
"Currency", if(CurrResult = 0, BLANK(), "$" & CurrResult & "K"),
""
)

 

Would anyone have any recommendation on how to handle the above three items? 

Thanks!

 

1 ACCEPTED SOLUTION
DarthTim
Frequent Visitor

I had tried to find a solution for this for a while, but shortly after I submitted this post, I stumbled across two pages which provided good insight on additional formatting of numbers in text fields. 

For resolving my three problems in the original post, I simply needed to update to what is highlighted in red below. I'm sharing to help others that might have the same question some day... 

 

SWITCH('Data Append'[Type],
"Percent", FORMAT('Data Append'[Value],"0.0%"),
"Decimal", FORMAT('Data Append'[Value],"0.00"),
"Number", FORMAT('Data Append'[Value],"#,0"),
"Currency", if(CurrResult = 0, BLANK(), FORMAT(CurrResult, "$#,0") & "K"),
""
)

 

A good listing of additional DAX formatting tips I had sent before:
https://dax.guide/format/ 


A few good examples of some of these formatting tips:
https://blog.crossjoin.co.uk/2019/09/11/power-bi-custom-format-string-examples-part-1-numbers/

 

View solution in original post

1 REPLY 1
DarthTim
Frequent Visitor

I had tried to find a solution for this for a while, but shortly after I submitted this post, I stumbled across two pages which provided good insight on additional formatting of numbers in text fields. 

For resolving my three problems in the original post, I simply needed to update to what is highlighted in red below. I'm sharing to help others that might have the same question some day... 

 

SWITCH('Data Append'[Type],
"Percent", FORMAT('Data Append'[Value],"0.0%"),
"Decimal", FORMAT('Data Append'[Value],"0.00"),
"Number", FORMAT('Data Append'[Value],"#,0"),
"Currency", if(CurrResult = 0, BLANK(), FORMAT(CurrResult, "$#,0") & "K"),
""
)

 

A good listing of additional DAX formatting tips I had sent before:
https://dax.guide/format/ 


A few good examples of some of these formatting tips:
https://blog.crossjoin.co.uk/2019/09/11/power-bi-custom-format-string-examples-part-1-numbers/

 

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors