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

format a measure to million without 'M'

Hi, I want to format a measure to millions without 'M'. Here under is the measure

 

 

Sales (Last Year) = CALCULATE(SUM(Query1[Gross Sales]),SAMEPERIODLASTYEAR(DimEnqDate[FullDate]))

 

 

I know this is the code for format

Column = FORMAT ( Table[Amount], "#,##0,,M" )

  But how should I apply it on the measure above so that it works with at least 'M' for a starter?

 

I have tried this

 

Sales (Last Year) = CALCULATE(SUM(Format(Query1[Gross Sales], "#,##0,,M")),SAMEPERIODLASTYEAR(DimEnqDate[FullDate]))

 and this

Sales (Last Year) = FORMAT(CALCULATE(SUM(Query1[Gross Sales]),SAMEPERIODLASTYEAR(DimEnqDate[FullDate])), "#,##0,,M")

 but the visual becomes blank.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @fasih,

 

I'd like to suggest you to take a look at following link to know how to customize format function.

 

Reference links:

FORMAT Function (DAX)

 

Sample formula: it will format 100 million(100,000,000) as 100

test =
FORMAT ( VALUE ( "100,000,000" ), "#,0,," )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
Sujith_S
Frequent Visitor

pls create a new calculated column and 

Column =
if('table1'[column]>=0,'table'[column]&"M").. so that u got the column with all values in Millions.

now simply drag it in a slicer and its done .. no measure needed !
v-shex-msft
Community Support
Community Support

Hi @fasih,

 

I'd like to suggest you to take a look at following link to know how to customize format function.

 

Reference links:

FORMAT Function (DAX)

 

Sample formula: it will format 100 million(100,000,000) as 100

test =
FORMAT ( VALUE ( "100,000,000" ), "#,0,," )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.