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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates