cancel
Showing results for
Did you mean:
Juramirez Member

Comma inside Round function in measure

Hi all

I have this measure which allows me to put in a card the total amount without all numbers:

Measure = IF(CALCULATE(SUM(Table[Amount]),ALLSELECTED(Table[Concept]))>999999,
CONCATENATE("\$",CONCATENATE(ROUND(CALCULATE(SUM(Table[Amount])/1000000,ALLSELECTED(Table[Field])),0)," millions")),
IF(ISBLANK(CALCULATE(SUM(Table[Amount]),ALLSELECTED(Table[Field]))),"Zero",
CONCATENATE("\$",CONCATENATE(ROUND(CALCULATE(SUM(Table[Amount])/1000,ALLSELECTED(Table[FIeld])),0)," k"))))

It gaves me values like this: \$1084 millions

What I want is to put a comma like this when first condition is met: \$1,084 millions | \$10,000 millions

How can i achieve it inside the measure?

Regards,

Julián

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support Team

Re: Comma inside Round function in measure

As tested, here is a workaround.

1.Create measures as below to replace the formula you use in your [measure].

ROUND(CALCULATE(SUM(Table[Amount])/1000000,ALLSELECTED(Table[Field])),0)->Measure2

ROUND(CALCULATE(SUM(Table[Amount])/1000,ALLSELECTED(Table[FIeld])),0)->Measure4

Measure2 = ROUND(CALCULATE(SUM('Table'[Amount])/1000000,ALLSELECTED('Table'[Field])),0)

Measure4 = ROUND( CALCULATE(SUM('Table'[Amount])/1000,ALLSELECTED('Table'[Field])),0)

2. displays the number with your currency locale formatting by measures below

Measure3 = FORMAT([Measure2], "Currency")

Measure5 = FORMAT([Measure4],"Currency")

3 concatenate the currency and "millions" or"k".

Measure

final output = IF(CALCULATE(SUM('Table'[Amount]),ALLSELECTED('Table'[Concept]))>999999,
CONCATENATE([Measure3]," millions"),
IF(ISBLANK(CALCULATE(SUM('Table'[Amount]),ALLSELECTED('Table'[Field]))),"Zero",
CONCATENATE([Measure5]," k"))) Or you could nest measures in one measure which works as lists of measures above .

Measure =
VAR Measure2 =
ROUND (
CALCULATE ( SUM ( 'Table'[Amount] ) / 1000000, ALLSELECTED ( 'Table'[Field] ) ),
0
)
VAR Measure4 =
ROUND (
CALCULATE ( SUM ( 'Table'[Amount] ) / 1000, ALLSELECTED ( 'Table'[Field] ) ),
0
)
VAR Measure3 =
FORMAT ( Measure2, "Currency" )
VAR Measure5 =
FORMAT ( Measure4, "Currency" )
RETURN
IF (
CALCULATE ( SUM ( 'Table'[Amount] ), ALLSELECTED ( 'Table'[Concept] ) ) > 999999,
CONCATENATE ( Measure3, " millions" ),
IF (
ISBLANK (
CALCULATE ( SUM ( 'Table'[Amount] ), ALLSELECTED ( 'Table'[Field] ) )
),
"Zero",
CONCATENATE ( Measure5, " k" )
)
)

Best Regards

Maggie

3 REPLIES 3 Community Support Team

Re: Comma inside Round function in measure

As tested, we could get the comma in nuber type value by "Modeling"->"Format"->comma, but it can be put into measure in text type. Best Regards

Maggie

Highlighted Community Support Team

Re: Comma inside Round function in measure

As tested, here is a workaround.

1.Create measures as below to replace the formula you use in your [measure].

ROUND(CALCULATE(SUM(Table[Amount])/1000000,ALLSELECTED(Table[Field])),0)->Measure2

ROUND(CALCULATE(SUM(Table[Amount])/1000,ALLSELECTED(Table[FIeld])),0)->Measure4

Measure2 = ROUND(CALCULATE(SUM('Table'[Amount])/1000000,ALLSELECTED('Table'[Field])),0)

Measure4 = ROUND( CALCULATE(SUM('Table'[Amount])/1000,ALLSELECTED('Table'[Field])),0)

2. displays the number with your currency locale formatting by measures below

Measure3 = FORMAT([Measure2], "Currency")

Measure5 = FORMAT([Measure4],"Currency")

3 concatenate the currency and "millions" or"k".

Measure

final output = IF(CALCULATE(SUM('Table'[Amount]),ALLSELECTED('Table'[Concept]))>999999,
CONCATENATE([Measure3]," millions"),
IF(ISBLANK(CALCULATE(SUM('Table'[Amount]),ALLSELECTED('Table'[Field]))),"Zero",
CONCATENATE([Measure5]," k"))) Or you could nest measures in one measure which works as lists of measures above .

Measure =
VAR Measure2 =
ROUND (
CALCULATE ( SUM ( 'Table'[Amount] ) / 1000000, ALLSELECTED ( 'Table'[Field] ) ),
0
)
VAR Measure4 =
ROUND (
CALCULATE ( SUM ( 'Table'[Amount] ) / 1000, ALLSELECTED ( 'Table'[Field] ) ),
0
)
VAR Measure3 =
FORMAT ( Measure2, "Currency" )
VAR Measure5 =
FORMAT ( Measure4, "Currency" )
RETURN
IF (
CALCULATE ( SUM ( 'Table'[Amount] ), ALLSELECTED ( 'Table'[Concept] ) ) > 999999,
CONCATENATE ( Measure3, " millions" ),
IF (
ISBLANK (
CALCULATE ( SUM ( 'Table'[Amount] ), ALLSELECTED ( 'Table'[Field] ) )
),
"Zero",
CONCATENATE ( Measure5, " k" )
)
)

Best Regards

Maggie

Juramirez Member

Re: Comma inside Round function in measure

Thanks for your answer @v-juanli-msft! It works!

Regards,

Julián

Announcements Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Top Kudoed Authors
Users Online
Currently online: 121 members 1,651 guests
Recent signins:
• Marcela_TC • trivita • AnnieShahid • KennethTsang • dianeos • RichelleZach • tlaresch • mariofalkonry • osyed • Abdul_Azlin • andrewmelder • retailbusiness • Veronica8481 • data_insights 