Hey folks,
I have four SharePoint Online tables that are part of an end to end business process, made up of four distinct stages. Those four tables are based on those stagea and each stage has a series of status 'toll gates'. Each table has fields unique to the given stage of the process that the table models, but there are also some common fields among all four fields. I.e., they all have different record layouts and number of records but there are a handful of columns that are exactly the same across each and contain the same type of responses.
I need to aggregate percentage complete by business unit across these tables. I have measures in each table to compute total records and total complete records. And I even created a table to aggregate all of my status measures to show the overall status of the Stages. However, I am not able to do any sort of business unit level aggregate reporting either at the total of all stages or at the individual stages.
Here is an example of one table showing only the common fields across all four tables. Table1 could have 25 columns whereas Table 2 could have 50 columns, etc.
recordStatus is calculated to look at the three statuses in a given table and if all status fields are complete then the record is complete. The three statuses are set manually by each users.
| bu_name | tblname | Status_1 | Status2 | Status3 | recordStatus |
| Name1 | Stage1 | | | 1 | 0 |
| Name2 | Stage1 | 1 | Agree | 1 | 1 |
| Name3 | Stage1 | 1 | Disagree | 1 | 1 |
| Name4 | Stage1 | | | 1 | 0 |
What I'd like to be able to do is have a table that helps me report on Overall Status by business unit and by business unit ~ stage.
Business Unit by Total Status (across all four stages)
| bu_name | Status |
| Name1 | 25% |
| Nam2 | 12% |
| Name3 | 89% |
| etc | 13% |
Business Unit1 ~ Stage (using dummy data)
| bu_name | stage | % Complete |
| Name1 | Stage1 | 12% |
| Name1 | Stage2 | 0% |
| Name1 | Stage3 | 5% |
| Name1 | Stage4 | 29% |
This post is sort of close: Solved: Re: Aggregating values from different tables to an... - Microsoft Fabric Community except I do not have total values in each table. Rather, I have indivual count data in each table that I need to total and bring into the aggregated table.
