Convert Decimals to Custom Fractions

[jmcconville]

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

[LivioLanzo]

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")

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-v...

[jmcconville]

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

[LivioLanzo · Accepted solution]

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
)