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:
- Unpivot all the metrics and the correspondent value from the original table to two columns called “metric” and “metric_value”
- 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.
- 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!