I feel like I’m missing something obvious but I can’t figure it out. I have several measures I want to compare in a donut/gauge style visual. The measures are, total quantity, remaining quantity, and billed quantity. Total quantity would be my target value, and I’m looking to see the remaining quantity and billed quantity compared to my total. It is important to see these three broken out into individual segments so I don’t want to sum two of the fields to make it work for the gauge since it can’t support more than one value. My next plan was to use a donut chart since you can break a total into multiple segments. But I’m having some trouble figuring out how to do this. The way the data is stored there are three separate columns for total, billed, and remaining and I can’t change that, but with the donut I can only add one value split by dimensions. I could use total as my one value, but I am not sure how I would create a dimension that would split out what portions of that total were billed, and remaining. I should also note that there is a fourth category, open, that I don’t have data for, so billed + remaining <> total. Does anybody know how to do this?
Hi @rbrady
I don’t understand your use case completely but you can use this approach to solve your split by dimension problem. After reviewing you should have what you need for your specific use case.
1. You will need a model of your measures that will be used for a full join.
measure model:
dataset:
2. Create a QuickSight dataset with source data and create a dataset calculated field.
c_link
3. Use your source dataset to create an additional dataset for your full join
4. Create a calculated field that will filter your column values by measure
c_value
ifelse(
measure='val_open' AND {val_billed}+{val_remaining}<>{val_total},({val_billed}+{val_remaining})-{val_total},
measure='val_total',{val_total},
measure='val_billed',{val_billed},
measure='val_remaining',{val_remaining},
0)
5. Create a calculated field that will split values by total or components
c_measure_grp
ifelse(
measure='val_open' OR measure='val_billed' OR measure='val_remaining','components','total')
Result:
1 Like
Thanks, That helps a lot!