This can be done easily in Tableau Prep. Basically, you want to create aggregates of the three values you want in the calculation. To give you an example, I used Tableau's Superstore sample to aggregate Sales by Category:
Then join the aggregation with the original file:
That will provide you with the Sales Sum by Category. You can use that same idea for your three calculations. Once everything is joined together you can then create a calculation of the three fields to get the result you are looking for. And Tableau Prep can then create the separate files you need.
Hi Barry and thank you for the answer,
Although this is a working solution it does not take in account the different periods but rather returns a result for the whole dataset. Obviously, I have failed to explain the whole picture and what we want to achieve.
We tried to create a separate datasource to determine a coefficient and in my sql query I did the following:
We group the data into periods, e.g. we have a dimension for each month/year included in the dataset (e.g. 01.Jan1.2018, 01.Feb.2018, 01.Mar.2018, etc.). Against this I am calculating a coefficient as follows:
Total Administrative expenses / Total income for all departments and am returning the following data (example)
Period (dimension) / Coefficient (measure)
01.Jan.2018 / 0.1752
01.Feb.2018 / 0.1684
01.Mar.2018 / 0.1727
01.Apr.2018 / 0.1798
Now having this coefficient I will be able to multiply the Department income by it and will receive the department's share of the Administrative expenses. This way, even if the department manager opens the file in Tableau Desktop they will not see any other data but the coefficient (and of course, their department's data). So our idea is achieved - other department's data is secured and every department have access only to their own data.
However, I am experiencing an issue with Tableau reading the code (sql). In MSSQL Server it takes a little more than a minute to return coefficients for the past 3 years but once we put in Tableau it freezes and counts for a long time withour returning a result (nor an error). My senior colleague will be working on creating a datasource with the coefficients and joining or blending them in Tableau.
I will mark your answer as correct as it answers my question in the way I am asking it
Barry, a last one: Can I achieve what I have explained here in Tableau Prep? Thanks!
Glad you found a solution. Yes, you can create the coefficient in Tableau Prep. Create aggregations for both Total Administrative Expenses and Total Income, using Period as the grouping. You can then use that to create a calculation for each department's administrative expenses.