cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmcconville Frequent Visitor
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. My Measure DAX Formula PowerBI2

 link Here is a screencast of what I'm doing

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

3 REPLIES 3
LivioLanzo Super Contributor
Super Contributor

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

jmcconville Frequent Visitor
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

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

LivioLanzo Super Contributor
Super Contributor

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 3,076 guests
Please welcome our newest community members: