I’m in a bit of a pickle. To give you a bit of background, I have been asked to design dashboards that will be used across a number of databases as such I need to avoid the use of parameters(more on that later) as they are not dynamic and I can’t be amending them every time I switch databases.
I need to be able to calculate an index on other forms of transport vs car. Key to this is that I need to be able to select what method of transport I want to index at a time.
- i.e. train vs car or cycle vs car.
Using parameters this is easy peasy, however as I've mentioned before I will need to switch databases and there maybe we have other elements like, airplane vs car or skate vs car. By the way I've mocked up this data, I don’t know anything about transport.
So I started by creating two calculated fields
!Car IF [Transport] = "Car" then [Value] END
!Other Transport IF [Transport] = "Car" then NULL else [Value] END
After I created another two calculated fields to give me a percent distribution
!Car as a % SUM([!Car]) / total(sum([!Car]))
!Other Transport as a % sum([!Other Transport]) / total(sum([!Other Transport]))
Important to note here, I can’t use LOD’s as I want the ability to give my end user filters for them to use.
Finally I calculated the Index
!Index Other on Car [!Other Transport as a %] / [!Car as %] * 100
Now the point is that, I want to give my end users the ability to decide what they are indexing against car and show only that. Car is always the indexer and the indexee is decided by the end user. Remember we cannot use parameters as they might change across databases and adding them all is not an option.
I’ve attached the workbook below
Thanks in advance
Index Transport.twbx 12.2 KB