cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

8 REPLIES 8
Super User II
Super User II

Hey,

 

mark your measure or calculated column in the fields pane.

 

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

Format of Meausres.png 

 

Hope this helps

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Resolver II
Resolver II

8-28-2017 1-41-27 PM.png

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

Frequent Visitor

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?

 

 

 

 

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

View solution in original post

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

 

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, %.

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
Not applicable

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

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors