Reply
Frequent Visitor
Posts: 2
Registered: ‎01-11-2019
Accepted Solution

Convert Decimals to Custom Fractions

[ Edited ]

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


Accepted Solutions
Super User
Posts: 751
Registered: ‎09-16-2018

Re: Convert Decimals to Custom Fractions

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!

View solution in original post


All Replies
Super User
Posts: 751
Registered: ‎09-16-2018

Re: Convert Decimals to Custom Fractions

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



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Frequent Visitor
Posts: 2
Registered: ‎01-11-2019

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

DAX.JPG

Here is what the correct calculation in excel is showing

excel.JPG

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 User
Posts: 751
Registered: ‎09-16-2018

Re: Convert Decimals to Custom Fractions

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!