- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Convert Decimals to Custom Fractions
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 08:24 AM - edited 01-11-2019 08:46 AM
I need the summarized values (on charts and tables) to show as a custom fraction. My organization counts contracts in 13 week incriments. IE 1 contract = 13 weeks. If a contract is less or more than 13 weeks then the remaining should show as a fraction. So if we have a contract 5 weeks long, we count that as 5/13. if it is 14 weeks long we count that as 1 1/13 etc. PowerBI
Example: I have a chart that sums the contracts and groups them by month. The problem is it's displaying the decimals and not allowing me to use a custom format for the results
In excel we can easily do this by choosing RightClick>Format Cells>Number>Custom> "# #/13" Excel
I've created a measure and attempted to format using DAX on a new column. It still won't sum the information. It must be because its not recognizing the field as a number anymore. My Measure DAX Formula PowerBI2
link Here is a screencast of what I'm doing
Solved! Go to Solution.
Accepted Solutions
Re: Convert Decimals to Custom Fractions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-23-2019 11:45 PM
Hi @jmcconville
you can alsways add it like this:
Column = VAR _decimal = 13 VAR _Remainder = MOD( [ContractsMeasure], 1 ) RETURN IF( _Remainder = 0, "" & [ContractsMeasure], [ContractsMeasure] & " " & ROUND( _Remainder * _decimal, 0) & "/" & _decimal )
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
All Replies
Re: Convert Decimals to Custom Fractions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-12-2019 03:35 AM
Hi @jmcconville
the FORMAT function in DAX is based on the format strings used by Visual Basic.
try this format string:
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: Convert Decimals to Custom Fractions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-23-2019 02:20 PM
Hi
@LivioLanzo . Thank you for your response
I've done the format you suggested and it's still not showing correctly. Here's what Power BI is showing
Here is what the correct calculation in excel is showing
Example: 755.38 = 755 5/13
the remainder (.38) divided by 13 is 5/13. Power BI is showing this as 4/13
let me know your thoughts! thanks
Re: Convert Decimals to Custom Fractions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-23-2019 11:45 PM
Hi @jmcconville
you can alsways add it like this:
Column = VAR _decimal = 13 VAR _Remainder = MOD( [ContractsMeasure], 1 ) RETURN IF( _Remainder = 0, "" & [ContractsMeasure], [ContractsMeasure] & " " & ROUND( _Remainder * _decimal, 0) & "/" & _decimal )
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!