Powerbi reporting builder sparkline not showing values with £ and % format

[zohra69]

Hi All 

I hope someone can assist me, i am creating a paginated report to enable the users access to their information. This is first time i am using powerbi report builder. The matrix populate correct data based on KPI list in the matrix table. However the sparkline does not seem to recognise some formats (£ and %) see the photos. I have created a calculated field in my dataset name Matrix_Scorecard_Practice_Actual_Combined with this expression =IIF(
Right(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "%",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "%", "")) / 100,
IIF(
Left(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "£",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "£", "")),
IIF(
IsNumeric(Fields!Matrix_Scorecard_Practice_Actual.Value),
CDbl(Fields!Matrix_Scorecard_Practice_Actual.Value),
Nothing
)
)
) 

I have added a column in my matrix to insert the sparkline, right click on the chart data value +icon to bind the calculated field =Sum(Fields!Matrix_Scorecard_Practice_Actual_Combined.Value), i have added under category group Financial quarter field. when running the report the sparkline only shows value format as whole or decimal number but not £value and or %value. I have tried everything and spend 3 days trying to find a solution. can anyone suggest an alternative and or if i am missing adding the format somewhere else

In advance thank you for your support

[Anonymous]

Hi @zohra69 ,

The calculated field you’ve created, , is correctly converting the values into a numeric format. However, when you’re binding this field to the sparkline chart, it’s treating all values as numeric, hence it’s not showing the £ and % symbols.

One possible solution could be to create two separate fields for the £ and % values. Then, you can create two separate sparklines for each of these fields. This way, you can maintain the original formatting of the values.

Best regards.
Community Support Team_Caitlyn

[zohra69]

---

@zohra69 wrote:

Hi All 

I hope someone can assist me, i am creating a paginated report to enable the users access to their information. This is first time i am using powerbi report builder. The matrix populate correct data based on KPI list in the matrix table. However the sparkline does not seem to recognise some formats (£ and %) see the photos. I have created a calculated field in my dataset name Matrix_Scorecard_Practice_Actual_Combined with this expression =IIF(
Right(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "%",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "%", "")) / 100,
IIF(
Left(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "£",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "£", "")),
IIF(
IsNumeric(Fields!Matrix_Scorecard_Practice_Actual.Value),
CDbl(Fields!Matrix_Scorecard_Practice_Actual.Value),
Nothing
)
)
) 

I have added a column in my matrix to insert the sparkline, right click on the chart data value +icon to bind the calculated field =Sum(Fields!Matrix_Scorecard_Practice_Actual_Combined.Value), i have added under category group Financial quarter field. when running the report the sparkline only shows value format as whole or decimal number but not £value and or %value. I have tried everything and spend 3 days trying to find a solution. can anyone suggest an alternative and or if i am missing adding the format somewhere else

In advance thank you for your support

---

---

@zohra69 wrote:

Hi All 

I hope someone can assist me, i am creating a paginated report to enable the users access to their information. This is first time i am using powerbi report builder. The matrix populate correct data based on KPI list in the matrix table. However the sparkline does not seem to recognise some formats (£ and %) see the photos. I have created a calculated field in my dataset name Matrix_Scorecard_Practice_Actual_Combined with this expression =IIF(
Right(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "%",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "%", "")) / 100,
IIF(
Left(Fields!Matrix_Scorecard_Practice_Actual.Value, 1) = "£",
CDbl(Replace(Fields!Matrix_Scorecard_Practice_Actual.Value, "£", "")),
IIF(
IsNumeric(Fields!Matrix_Scorecard_Practice_Actual.Value),
CDbl(Fields!Matrix_Scorecard_Practice_Actual.Value),
Nothing
)
)
) 

I have added a column in my matrix to insert the sparkline, right click on the chart data value +icon to bind the calculated field =Sum(Fields!Matrix_Scorecard_Practice_Actual_Combined.Value), i have added under category group Financial quarter field. when running the report the sparkline only shows value format as whole or decimal number but not £value and or %value. I have tried everything and spend 3 days trying to find a solution. can anyone suggest an alternative and or if i am missing adding the format somewhere else

In advance thank you for your support

---

thanks Caitlyn to respond promptly, however the idea of the matrix is to have only 1 sparkline against each KPI row, will be confusing to have additional in the report as you suggested