Forum Discussion
Convert Decimals to Custom Fractions
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
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 )
3 Replies
Hi jmcconville
the FORMAT function in DAX is based on the format strings used by Visual Basic.
try this format string:
FORMAT( 1893.15, "# .#/13")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
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 )
