Dear all,
I am looking for a way to allow multiple selections on the same "field". One selection is forced as a "Always one selected" and provides a baseline for comparison, while the second selection can be multiple choices. What I would like to achieve is a chart showing the selected second-selection, and the ratio between the baseline selection and the second selection.
For example, the following data:
| Product | Sales | Margin |
| A1 | 1000 | 100 |
| B2 | 500 | 80 |
| C3 | 1500 | 50 |
| D4 | 100 | 5 |
Selected BaselineProduct: A1
Selected Products: B2, C3
Chart:
| Product | Sales | Baseline Sales | Ratio |
| B2 | 500 | 1000 | 50% |
| C3 | 1500 | 1000 | 100% |
What I've done so far, with limited success, was make a second copy of the Product field (Load distinct Product as BaselineProduct resident Sales), set it as "Always one selected", and then assorted versions of set analysis in the chart. My best effort to get the baseline value ended up being:
=sum({$<Product= {$(=only(BaselineProduct))} >} Sales)
Which does indeed get the baseline sales correctly when Product is not a dimension. Unfortunately, when using Product as a dimension, this results in the following chart:
| Product | Sales | Baseline Sales | Ratio |
| A1 | - | 1000 | - |
| B2 | 500 | - | - |
| C3 | 1500 | - | - |
which is not what I had in mind.
I'd appreciate any suggestions as to how to get this done (perhaps using variables? Different set analysis? something else?).
TIA,
Or
