Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

0

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

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

zohra69_0-1720442815979.pngzohra69_1-1720442883976.png

In advance thank you for your support

 

Status: Delivered

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

Comments
v-xiaoyan-msft
Community Support
Status changed to: Delivered

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
Frequent Visitor

@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

zohra69_0-1720442815979.pngzohra69_1-1720442883976.png

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

zohra69_0-1720442815979.pngzohra69_1-1720442883976.png

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