Active / Projected project count

[AnthonyJoseph]

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

[tamerj1 · Accepted solution]

Hi @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.