Forum Discussion
Active / Projected project count
Hello Community,
I have two tables (Active and Projected) one table shows the count of projects that were projected and the "Active" table will give the count of projects that are active. I would need to show a fraction of "number of projects in Active table" / "number of projects in Projected table" based on the slicer selections from "Client, Portfolio and Year".
I have pasted below the sample data along with the expected output... anyhelp on how to achieve this is highly appreciated.
| Projected | |||
| Client | Portfolio | No of Projects | Year |
| A | Medical | 1 | 2019 |
| A | Insurance | 2 | 2019 |
| A | Education | 1 | 2019 |
| B | Medical | 1 | 2020 |
| B | Automobile | 3 | 2020 |
| C | Petroleum | 2 | 2020 |
| C | Medical | 1 | 2020 |
| C | Education | 1 | 2020 |
| A | Medical | 1 | 2020 |
| A | Insurance | 1 | 2020 |
| A | Education | 1 | 2020 |
| Active | |||
| Client | Portfolio | No of Projects | Year |
| A | Medical | 1 | 2019 |
| B | Medical | 1 | 2020 |
| C | Medical | 1 | 2020 |
| C | Education | 1 | 2020 |
| A | Medical | 1 | 2020 |
| A | Insurance | 1 | 2020 |
| A | Education | 1 | 2020 |
Output: when year = 2020 (multi-select) and client = B and C then output should be 3/8.
i.e. (sumf of projects in active table)/(sum of projects in Projected table).
Highlighted in yellow in below image.
Any help is highly appreciated.
Thanks,
AnthonyJoseph
Client, Portfolio and Year should be independent dimension tables connected to both fact tables. Then simple DIVIDE of the SUM's shsll do the job.
1 Reply
Client, Portfolio and Year should be independent dimension tables connected to both fact tables. Then simple DIVIDE of the SUM's shsll do the job.