CODEWITHSUNDEEP
excelformatpowerbidax
Joe McConville
Joe McConville

Reputation: 43

DAX Custom Number Format

I'm stumped on a seemingly simple format issue for Power BI. I'm trying to format the remainder of a number into a custom fraction (13ths)

For example: 755.38 should show as 755 5/13

Excel Format

In Excel, I use the custom fraction formula "# #/13"

In DAX I'm using the FORMAT function with the format as "#. #/13".

DAXFormat

This just takes the remainder and displays it with a "/13" on the end. it does not divide the remainder correctly.

Example 755.38 is showing as 755. 4/13 when the correct calculation is 755 5/13

Upvotes: 4

Views: 3812

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

I don't know that you can do it with just FORMAT, but you can definitely put the pieces together yourself. For example,

Measure = 
VAR Num = SUM(Table1[Number])
VAR Frac = ROUND(13*(Num - INT(Num)), 0)
RETURN FORMAT(INT(Num), "0 ") & Frac & "/13"

Samle Output

Upvotes: 3

teylyn
teylyn

Reputation: 35915

Excel and DAX formatting strings are not identical. The documentation for Excel custom formats has examples for fractions, but the DAX documentation for the FORMAT function has no reference to fractions, so it appears that fractions as they are displayed in Excel are not possible with DAX.

Excel documentation

DAX FORMAT documentation

Upvotes: 1

Related Questions