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

3 REPLIES 3
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.

Dear Xiaoxin Sheng,

 

What if I want to display one decimal after the 100M, for example:

 

100,000,000 as 100,0 or

1,203,000 as 120,3

 

Thank you!!!

 

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.