$ sign currency for only few rows based upon condition

[Anonymous]

Hi @all,

Please let me know if I can make changes to the currency only for few values in a column. 
For Example: If the type is Quantity then Value is 39. If the Type is Dollar then value is $540.

Type	Value
Quantity	39
Dollar	$540

Let me know with the possiblity. Thanks in advance.

[PaulDBrown · Accepted solution]

@Anonymous 

you can duplicate the measure and format each accordingly using the formatting options under "Measures tools" in the ribbon. Then use SWITCH to deliver the required output:

Measure format = SWITCH(TRUE(),

SELECTEDVALUE(table column [type]) = "Quantity", [number formatted measure],

[dollar formatted measure])

[Anonymous]

Hi @PaulDBrown ,

Can I fix the Decimal point to 2 in the in the Format with Currency ?

Currently it is not showing the decimal point as underlined with red in above screenshot.

[PaulDBrown]

@Anonymous 

Sure. Select the measure, got to "Measure tools" in the ribbon and in the box for decimals in the formatting section type in 2.

[Anonymous]

@PaulDBrown ,

I had did that but it works for quantity value. 
It is not working on format $

[PaulDBrown]

@Anonymous 

Make sure the field/value is formatted as "None" under "Display Units" in the formatting pane.

[Anonymous]

Hi @PaulDBrown ,

Yes The Display units is "None"  in field formating. I see there is no 2 decimal points in your visual under Sum of Target column.

[PaulDBrown]

@Anonymous 

Sorry, I should have clarified... I live in a land where the thousands seperator is defined by a " ." and the decimals by a " , " (so you can see there are indeed two decimal places in the values under sum of target).

So I'm not sure what is going on at your end. I take it the column itself is actually a number format (not "general" or "text")

[Anonymous]

Thanks for clarifying about seperator @PaulDBrown . 
Seperator for Thousands here is " , " and decimal is " . " so I was confused in checking that.

[Anonymous]

Thanks @PaulDBrown . That really worked.

[danextian]

Hi @Anonymous ,

You can create a calculated column but this will return a text and cannot be summed up.

Calculated Column =
IF (
    'Table'[Type] = "Quantity",
    FORMAT ( 'Table'[Value], "#,#" ),
    IF ( 'Table'[Type] = "Dollar", FORMAT ( 'Table'[Value], "$#,#" ) )
)

Alternatively, you can instead use a measure to aggregate Value and then format it:

Measure =
VAR __VALUE =
    SUM ( 'Table'[Value] )
VAR __TYPE =
    //there must be a single selected value in the current filter context or this will return a blank result
    SELECTEDVALUE ( 'Table'[Type] )
RETURN
    IF (
        __TYPE = "Quantity",
        FORMAT ( __VALUE, "#,#" ),
        IF ( __TYPE = "Dollar", FORMAT ( __VALUE, "$#,#" ) )

[Anonymous]

Hi @danextian ,

Thanks for the reply. I tried using the below and shows the error in the screenshot. Can you help me with that ?

Calculated column = IF(FinalTable[Type] == "Quantity1" || FinalTable[Type]=="Quantity2", IF(FinalTable[Value] == "" || ISBLANK(FinalTable[Value]), 0, FORMAT(FinalTable[Value], "#,#")), IF(FinalTable[Value] == "" ||ISBLANK(FinalTable[Value]) , 0, FORMAT(FinalTable[Value], "$#,##0;($#,##0)")))

Error: DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.