Forum Discussion
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...)
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
10 Replies
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
- TomMartens
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:
Hope this helps
Regards
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.
