How to change the format of a cell given a value of a parameter

Question & Answer
1

Hi, is there a way to change the format in a pivot table (for example) if the user change a parameter in the control bar?

For example, I have control which name is KPI and the user can select Spend and ROI.

  • If the user selects Spend, then the cells will show the values as $ dollar
  • If the user selects ROI, then the cells will show the values as % percentage

Thanks!

2

Hi @nftd ,

You can implement this in two different ways.

  1. Calculated field:
    You can create a calculated field with an expression similar tot he following where ‘test’ is a parameter associated with a control, and ‘Forecasted Monthly Revenue’ is the field value that has to be shown in $ currency or percentage.

ifelse(${test}=‘Spend’,concat(‘$’,toString({Forecasted Monthly Revenue})),concat(toString({Forecasted Monthly Revenue}*100),‘%’))

Please note that this might not format the values with separators. For example, the value would look like 2040000%, $20677 etc.

  1. Conditional rendering.
    This option involves creating two different pivot tables, layered one over the other and shown/hidden based on parameter control value selection. This would allow you additional customization of fields, separators etc so you can format the pivot table as per your requirement.
    A sample and how to is provided in our demo central site here.
1 Like
3

I love the second option! Thank you so much.

1 Like