I struggle while doing the stacked bar combo chart. See the details below:
The way the dataset is built twofold - the fact table contains metrics in rows, plus there is a hierarchy for these metrics, around 6 levels (top to bottom).
While building the combo chart, I want some to be placed as bars and just one on lines. Call it forecast.
Yet, if I filter metrics from bars, they will disappear from lines.
The only workaround which works was to expand metrics values from one to two columns along with using nulls naming. This way, bars have metrics + null forecast (#1 new column) while lines I have forecast while the rest is empty (#2 new column). Yet this is so an ugly solution…
I tried to use the PRE_FILTER but the thing is that I want some filters to be applied to lines (forecast). What I’ve been missing is some sort of conditional formatting while building the stacked bar combo chart.
Does any of you have a more elegant solution to build a stacked bar combo chart and apply filters for some of the data? Changing the structure of the metric table is not considered.
Can you please explain in more details why PRE_FILTER calculated column does not meet your needs?
You said that you want “some filters” to be applied. What is the logic to decide if a specific filter should be applied when computing the line values vs being ignored? How do you imagine providing this information to the QuickSight system?
TL/DR
From a total of 5 filters, I want 4 to be applied while one to be ignored.
Nevertheless, I should have avoided saying anything about PRE_FILTER as it causes confusion. The thing is that it will not solve anything. I did say that because even if it works, although it did not in my case, it will require the addition of new calculated fields.
I assume you have a solution on how to apply PRE_FILTER along with some filters and that is why you asked me about the PRE_FILTER. Unfortunately, I do not know how to combine PRE_FILTER and filter in one calculated field.
I don’t think QuickSight provides a way for you to achieve the result that you want without changing dataset schema.
When I’ve asked you about PRE_FILTER, I was trying to see if there is a functionality that we can provide in the future to make it easier to achieve the result you are looking for.
Kinda problematic, given that in some cases you have all the metrics in a row. Lack of this functionality limits all the visualizations which require rows to be split such as Guage or Combo charts.
