Concatenating decimal value column with '%' when a string column contains '%' in it.

[naveenpadala]

data view

visual view

Above is the table i have. Amount column is storing values as decimals which includes percentage values also (see SubCategory column). but i want to show '%' symbol beside the percentage value in Amount column wherever the SubCategory column value contains '%' in it. Its a Matrix visual.

[Ritaf1983 · Accepted solution]

Hi @naveenpadala 
if I understood you correctly you can surround your measure with "If"
Like : 

formated_% = if(CONTAINSSTRING(SELECTEDVALUE('Table'[category]),"%"),FORMAT([your measure_],"0.0%"),FORMAT([your measure_],"0"))

Pbix attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @naveenpadala 
if I understood you correctly you can surround your measure with "If"
Like : 

formated_% = if(CONTAINSSTRING(SELECTEDVALUE('Table'[category]),"%"),FORMAT([your measure_],"0.0%"),FORMAT([your measure_],"0"))

Pbix attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @naveenpadala 

I attached a pbix to the first response, you can download it and check my steps to compare with yours .
Also you you can attach some sample data / link to dummy pbix and i will look at why it didn't work.

[naveenpadala]

hi @Ritaf1983 ,

please find the dataset in Visits.pbix file in the below link.

Visits.pbix

[Ritaf1983 · Accepted solution]

Hi @naveenpadala 
Please update the formula to :

formated_%2 = if(CONTAINSSTRING(SELECTEDVALUE('data'[SubCategory]),"%"),FORMAT(SUM([Sum of Amount]),"0.00")&"%",FORMAT(sum(data[Sum of Amount]),"0.00"))

The updated PBIX attached 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[naveenpadala]

Thank you @Ritaf1983, it worked this time.

[Ritaf1983]

Glad to help ☺️

[naveenpadala]

hi,

This method did not work. '%' is not concatenated to the 'Amount' values.

[123abc]

To display the '%' symbol beside the percentage value in the "Amount" column whenever the "SubCategory" column contains '%' in it in a Matrix visual, you can use the Power BI DAX (Data Analysis Expressions) language to create a calculated column or measure. Here's a step-by-step guide to achieving this:

1. Open your Power BI file and go to the "Data" view.

2. Create a new calculated column in your data model. To do this, select the "Model" view, find your table, and then right-click on the table. Choose "New Column."

3. Use a DAX formula to create the calculated column. You can use the following formula:

Amount with Percentage = IF(ISNUMBER(SEARCH("%", YourTable[SubCategory])), YourTable[Amount] & "%", YourTable[Amount])

Replace "YourTable" with the name of your table, and "SubCategory" and "Amount" with the actual column names in your table.

The formula checks if the "SubCategory" column contains the '%' symbol. If it does, it appends '%' to the "Amount" value; otherwise, it leaves the "Amount" as it is.

1. Once you've created the calculated column, save your changes and return to the "Visual" view.

2. In the Matrix visual, use the new "Amount with Percentage" column in place of the original "Amount" column. You can drag and drop it into the Values section of the Matrix visual.

Now, your Matrix visual should display the '%' symbol beside the percentage value in the "Amount" column wherever the "SubCategory" column contains '%' in it.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[naveenpadala]

hi,

This is the error i face when i try your method.