I have an issue with the format of the database (for controlling purposes) I created which does not seem adapted for Tableau.
Indeed, I first built a first database on Excel only with all dimensions on a row and with one KPI per column. So it was displayed like “stairs” and then, when I uploaded this file on Tableau it worked quite well because I had all my KPIs directly available in “Measures”. I also have to display to kinds of currency which should be viewed as Filters on Tableau. So, in my Excel file, I have “EURO” and “entity currency” positioned on top of each other.
However, I can not use only Excel anymore because this is not powerful enough and we want to compile data from various sources and limit the calculations on Excel. As a result, I have to retrieve the core data from Excel but then compile everything on Access and also do some calculations on Access and create links between various tables. But Access works differently from Excel and to me, we have to have all our data in one column to be able to do calculations then. Concerning the currency, I created one column for the euro values and another one for the entity currency thanks to a formula linked to an exchange rates.
Here it is how it looks like:
The problem is when I upload this file on Tableau:
Indeed, I do not have one measure per KPI anymore so I can not build graphs with several KPIs. I can only display them as filters but if I click on more than one then it sums them and that is not what I want.
I only have one measure for euro values and one for local currencies (“LC”) which does not allow me anymore to set a filter per currency.
I could not find any article concerning the constraints for building databases for tableau (do KPIs have to be on different columns, presented as stairs in my Access file…).
Also, could you tell me the constraints to build my datamodel on Access in order to be able to have graphs displaying various KPIs over years etc?
I remain at your disposal for further information.
Thank you very much in advance,