How to display Currency symbol ($) for a selected Measure

DAX
1

Hi,

I have encountered with a situation where I have a line chat visual in my Power BI report. I use a Measure to show various trends based in the selection.

Here is the DAX which I used to display the trends based on selection.

Selected Metric Test =
var _selgroup = SELECTEDVALUE('Metric'[Metric (groups)])
return
IF(_selgroup = "Buy Down", [Avg Buy Down Test 1],
IF(_selgroup = "Cost Exposure", [Cost Exposure],
IF(_selgroup = "FOOP", [FOOP],
IF(_selgroup = "PClaims", [PClaims],
IF(_selgroup = "RRate", [RRate],
IF(_selgroup = "RevRate", [RevRate],
IF(_selgroup = "PRate", [PRate],0)))))))

In this “Buy Down” line should show in currency i.e value should be prefixed with $

I tried the following ways but it is not working

IF( _selgroup = "Buy Down", FORMAT([Avg Buy Down Test 1], "$#,##0.00"),

Can someone suggest me how to get the $ sign in front of the “Buy Down” value.

This measure [Avg Buy Down Test 1] I made into currency Data type.

Your help is highly appreciated.

Thanks.

1 Like
2

Use CONCATENATE

3

Hello @Bq1BQfLi

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

Hi @PankajNamekar

I have tried CONCATENATE as well but not use. It is blocking entire visual as blank.

Thanks

5

Hi @Bq1BQfLi,

Can you try this:

To display the “Buy Down” line in currency format with the $ sign, you can modify your DAX expression as follows:

Selected Metric Test =
VAR _selgroup = SELECTEDVALUE('Metric'[Metric (groups)])
RETURN
IF(
_selgroup = "Buy Down",
FORMAT([Avg Buy Down Test 1], "$#,##0.00"),
IF(
_selgroup = "Cost Exposure", [Cost Exposure],
IF(
_selgroup = "FOOP", [FOOP],
IF(
_selgroup = "PClaims", [PClaims],
IF(
_selgroup = "RRate", [RRate],
IF(
_selgroup = "RevRate", [RevRate],
IF(
_selgroup = "PRate", [PRate],
0
)
)
)
)
)
)
)

By using the FORMAT function within the “Buy Down” branch of the IF statement, you can apply the desired formatting to the value of [Avg Buy Down Test 1]. The “$#,##0.00” format code specifies that the value should be displayed as currency with two decimal places and a comma separator for thousands.

Make sure that [Avg Buy Down Test 1] is defined as a currency data type, as you mentioned, for the formatting to work correctly.

With this modification, the “Buy Down” line should display the value with the $ sign prefixed.

6

I have followed this format option earlier it self if you see my thread I mentioned that I have used this format option as you mentioned but when I’m using this entire visual is becoming blank.

7

hi @Bq1BQfLi

Can you share you working pbix file? or a dummy one? so others can check as well?

8

Hi @Bq1BQfLi - For this to work, you need to make formatting of “Selected Metric Test” dynamic. For this refer to newly introduced “Dynamic formatting” option by Power BI in April 2023. Refer to SQL BI article or other similar ones available online.

if still face issues, share sample PBIX file and I can try for you.

Thanks
Ankit J

1 Like
9

Hi @Bq1BQfLi

Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.