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

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

10 REPLIES 10
keerajvel
New Member

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

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.

DSP
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?

 

 

 

 

Anonymous
Not applicable

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

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?

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

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.

 

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
Super User
Super User

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

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.