How to present data in different unit if the data is stored in same column?

Here is my case, I tried to complete a request and below is the process and blocker:

  • Request : Create a Metric Filter allowing users to custom the metric for comparison
  • Operation Process:
  1. Unpivot all the metrics and the correspondent value from the original table to two columns called “metric” and “metric_value”
  2. Encounter the first blocker: Different metrics have different unit, such as: percentage (%), Million (MM). However, since I unpivot the metric value into one single column, I’m not able to custom units for different metrics.
  3. Tried to solve first blocker by creating three calculated field: Rate, Number, Dollar, to assigned the unit of each metrics. For example, for the percentage kind of metrics, I created a calculated field called “Rate”. Below are the example:

ifelse(
${metric}=‘ctr’,{metric_value},
${metric}=‘fill_rate’,{metric_value},
${metric}=‘roas’,{metric_value},
${metric}=‘sold_fill’,{metric_value},
${metric}=‘view_rate’,{metric_value},
NULL)

After creating the “Rate”, “Number”, “Dollar” calculated field, I was planning to create another Calculated Field call “UnitTrandform” to turn the number into text and transform them into the preferred format, here is what the “UnitTransform” calculated field supposed to look like:

ifelse(

isNotNull(max(Dollars)),
ifelse(max({Dollars})>=1000000000,
concat(‘$’,left(toString(max({Dollars})/1000000000),locate(toString(max({Dollars})/1000000000),‘.’)+2),‘B’),

max({Dollars})>=1000000,
concat(‘$’,left(toString(max({Dollars})/1000000),locate(toString(max({Dollars})/1000000),‘.’)+2),‘MM’),

max({Dollars})>=1000,
concat(‘$’,left(toString(max({Dollars})/1000),locate(toString(max({Dollars})/1000),‘.’)+2),‘k’),

concat(‘$’,left(toString(max({Dollars})),locate(toString(max({Dollars})),‘.’)-1))),

isNotNull(max(Numbers)),
ifelse(max({Numbers})>=1000000000,
concat(left(toString(max({Numbers})/1000000000),locate(toString(max({Numbers})/1000000000),‘.’)+2),‘B’),

max({Numbers})>=1000000,
concat(left(toString(max({Numbers})/1000000),locate(toString(max({Numbers})/1000000),‘.’)+2),‘MM’),

max({Numbers})>=1000,
concat(left(toString(max({Numbers})/1000),locate(toString(max({Numbers})/1000),‘.’)+2),‘k’),
concat(toString(max({Numbers})),’ ')),

concat(left(toString({Percent}*100),locate(toString({Percent}*100),‘.’)+1),‘%’)
)

However, the second blocker I faced is that I’m not able to create any of the “Rate”, “Number”, “Dollar” calculated field because the “Metric” parameter I used is a multi-select dropdown list and Quicksight seems not available to create calculated field with multi-select dropdown parameter.

I’ll be super grateful and appreciate if anyone has an idea on how to present data in different unit if the data is stored in same column. Here is the example of the dashboard, Purple is what the data currently looks like and red is what I want the data to be looked.

Thank you all and Happy Thanksgiving!

Hi @cyhsiao - If I understood your question correctly, here is something you can try. May be you can try by creating a duplicate metric_value column and then assign it to the parameter for each of the values you are comparing. Then assign the appropriate unit for each of the columns.

@cyhsiao
Did this solution work for you? I am marking the reply as “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight CommunityQ&A Forum!

Hi @spaluri , Thank you for the reply!
Do you mean that I can create metric_value calculated field for each of the metric because it was unpivot into one column?
If so, that is not applicable to my case, the original table stores different metrics’ value in different columns, but I unpivot them into two columns, one column store the metric name and the other store the metric value. I did this because I want to create a multi-selection filter to allow users select the metrics they want to see. By doing so I can set the metric name as filter.

To sum up, in order to create a multi-selection filter for metrics, I need my data to be presented in two columns, one column store the metric name and the other store the metric value. But in this way, it cause another problem that I’m not able to custom unit (%, $(MM), …etc) for each individual metrics. So I tried to follow the above solution but still not work.

Thank you so much for the help! I appreciate any advices!