cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Juramirez Member
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
Community Support Team
Community Support Team

Re: Comma inside Round function in measure

Hi @Juramirez

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

4.png

 

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
Highlighted
Community Support Team
Community Support Team

Re: Comma inside Round function in measure

Hi @Juramirez

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.

2.png

 

 

Best Regards

Maggie

Community Support Team
Community Support Team

Re: Comma inside Round function in measure

Hi @Juramirez

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

4.png

 

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
Member

Re: Comma inside Round function in measure

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

 

Regards,

Julián

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 231 members 2,396 guests
Please welcome our newest community members: