cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.DAX Formula PowerBI2

link Here is a screencast of what I'm doing

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Convert Decimals to Custom Fractions

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

Proud to be a Datanaut!

3 REPLIES 3
Super Contributor

## Re: Convert Decimals to Custom Fractions

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...

Proud to be a Datanaut!

Frequent Visitor

## Re: Convert Decimals to Custom Fractions

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

Super Contributor

## Re: Convert Decimals to Custom Fractions

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

Proud to be a Datanaut!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,076 guests
Recent signins: