Forum Discussion

DSP's avatar
DSP
Frequent Visitor
9 years ago
Solved

Measures - editing number formats

Very new to PBI - I've downloaded the latest version.   I've imported an Excel workbook with Powerpivot model into PBI, set up a matrix, and added a couple of fields and a Total Sales Amount measur...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi,

     

    If you use DAX to create a measure for the field you are using you can wrap that in the format function, e.g.

    Measure = FORMAT( SUM( Table[Sales]), $#,##0;($#,##0) )

     

    The format function can take user defined formats as shown above (above is brackets for negative, comma separated dollar format.
    For a bigger list of user defined function see here:

     

    https://msdn.microsoft.com/en-us/library/ee634206.aspx

     

    Hope this helps,

     

    Will

ChrissyG's avatar
ChrissyG
New Member
8 years ago

Hi, I am trying to format my measure with 0.00%  but it is giving me an error. Can you please help? 

(I have tried 0% to just simplify but still get error)

 

I have tried a few things: 

MTD Rev Var % = Format((Divide([MTD Rev Var],[LY MTD Rev])),0%)

MTD Rev Var % = Format(Divide([MTD Rev Var],[LY MTD Rev]),0.00%)

 

Error message: 

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 46, %.

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 45, %.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi,

 

Do you definitely need to use the format function?

It looks like you could just create your measure (without the format function) and then format the measure itself as shown in a previous answer on this thread.

 

I would recommend doing this as the format function creates a string in the correct format but formatting the measure leaves it as a number, which is often more useful.

 

Thanks,

 

Will

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    'the format function creates a string in the correct format but formatting the measure leaves it as a number'

     

    Thanks for this. I couldn't understand why using FORMAT wasn't working for me and now I do. I've used the option in Modeling.