Formating a table

[Richard76]

Hi Folks,

new to power BI and just designing my first reports. I have read a lot of atricles online but cant seem to find a solution for what would seem a very basic formatting request. I am getting my data from SQL server in tabular form and have created a simple sales table. I have formatted the data to £ which is fine but the numbers are coming through as say £3,000,000 when I want it to say £3000k in order to reduce the clutter in the table. 

Surely a simple solution ??

Thanks

Richard

[v-xjiin-msft]

Hi @Richard76,

Currently, all the built-in formats are listed in the Format option in Modeling pane. If there doesn't exist the format like £3000k, it means that we cannot achieve it with built-in ways.

So to achieve your requirement, we need some workarounds. We should first define an unit and manually format the original currency to your wanted format. Please refer to following calculated column:

Formatted amount =
IF (
    Amount[Amount] > 3000,
    "£" & DIVIDE ( Amount[Amount], 1000 )
        & "K",
    "£" & FORMAT ( Amount[Amount], "####" )
)

However, by doing this the new column will be displayed as text type no longer numbers. Because there's no such format in numbers column. If you still want to keep numbers column with your wanted format. I'm afraid currently it is not supported in Power BI desktop. And I would suggest you to submit an idea here for a feature request.

Thanks,
Xi Jin.

[Sachy123]

You will need use if clause in the DAX query in your model something like below

[Richard76]

Really ?? So if I have 12 columns I need to format I need to add in 12 new columns together with the formule below. 

I thought it would be a click of a button job ! 

[Sachy123 · Accepted solution]

Create a new Column in the datamodel and use if statment while formatting

something like this

[url=https://imgbb.com/][img]https://image.ibb.co/kPnzsn/Formatting.png[/img][/url]