Measures - editing number formats

[DSP]

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 measure in the values area.

I now want to change the formatting of Total Sales Amount.

In Powerpivot, (Excel 2013) I would go into Manage Calculated Fields, and edit the measure ithere.

What do I do in PBI? There's no equivalent of  Manage Calculated Fields in any of the ribbons. I've right-clicked on the measure in the fields list, but that doesn't show any kind of 'manage fields' link.

(I did say I was very new...)

[keerajvel]

I have a measure that outputs value as say 24.56k. I want to create a custom format #,##0.00;-#,##0.00. How do I achieve this in Power BI? The FORMAT function changes the data type to text which is not what I want. Thanks for any help you can offer.

[Anonymous]

Within PowerBI, click on the measure and then go to Modeling on the menu. You'll see a Formatting section. Make changes there.

[DSP]

That's great - but what I had in mind is the custom formatting you have in Excel.

For example, to show numbers in round thousands: #,##0 

You could always divide by 1,000 to get the same effect in this particular case - but what about putting negative numbers in brackets?

[Anonymous · Accepted solution]

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

[aausten22]

Hi, I am wanting to format numbers into $#,k so the result would be 4.2k for example

How ould this be done with the custom format in the measure?

[ChrissyG]

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]

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]

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

[DHoban]

I am using Direct Query and I have already created a calculated field called Vendor Count (i.e. distinct Vendor count).  I simply want to format the number "standard".   1) I don't see a drop down option that lets me do that  2) I also do not see where I can 'edit' the field to write the appropriate DAX syntax.   Ideas?  ~Denise

[TomMartens]

Hey,

mark your measure or calculated column in the fields pane.

From the modeling menu you can change the format of your measure:

 

Hope this helps

Regards