Forum Discussion
Access calculated table in multiple reports
Hi People, Currently I'm struggling with the problem of repeating calculated tables in multiple reports. I have used a few datasets from a SQL sever and Excel files to calculate the final datase...
Thank you for your responses.
I will give additional explanation of the problem with an example:
Note: all tables are imported in PBI desktop and afterwards the report is published to the PBI webservice.
The purpose of this report is to define which bonus you can expect from a vendor at the end of a year.
Table 1 (based on a SQL script)
| Vendor | Date | Turnover |
| vendor A | 1-1-2020 | €10000 |
| Vendor B | 1-2-2020 | €5000 |
| Vendor A | 1-2-2020 | €5000 |
| vendor A | 1-3-2020 | €10000 |
| Vendor B | 1-4-2020 | €5000 |
| Vendor A | 1-3-2020 | €7500 |
Table 2 (Excel file):
| Vendor | Zone 1 | Zone 2 | Zone 3 | Result zone 1 | Result zone 2 | Result zone 3 |
| vendor A | €10000 | €20000 | €40000 | 0 | 5% | 7,50% |
| Vendor B | €20000 | €50000 | €70000 | 0 | 10% | 11,50% |
Calculatedtable1 (calculated table, which is created after the import of table 1 and 2 / after the query editor):
| Vendor | Turnover | Bonus |
| Vendor A | € 32.500 | € 2.438 |
| Vendor B | € 10.000 | € - |
This table is created based on the following script:
CalculateTable1 = SUMMARIZE(Tabel1;Tabel1[Vendor];"Turnover";CALCULATE(sum(Tabel1[Turnover])))
The bonus column is a calculated column based on the following script:
Bonus = SWITCH(TRUE();
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 1];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 1];Tabel2[Vendor];CalculateTable1[Vendor]);
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 2];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 2];Tabel2[Vendor];CalculateTable1[Vendor]);
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 3];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 3];Tabel2[Vendor];CalculateTable1[Vendor]))
This is the simple version of the real calculated table.
So the problem is, for every report I have to repeat the import process of the SQL script and the Excel file and afterwards I have to recreate the calculated table and the calculated columns.
Is it possible to share the calculated table or the complete import process with other reports? And is it possible to also add other datasets to that second report?
I have tried the option "Power BI-datasets" in Power BI Desktop, but it is not possible to add additional datasources in the report.
