Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmcconville
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

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!  

View solution in original post

3 REPLIES 3
LivioLanzo
Solution Sage
Solution Sage

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!  

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.