Calculating the rate at each time point

[AC422]

I am new to Power BI.

I have 2 datasets that I need to calculate the deviation rate for each site number based on the total number of deviations and the total number of people treated at the same time point.

For example, in 2024 Q2, site number 105 had a total of 3 deviations; only 1 person was treated. The rate should then be 3. By 2025 Q1, site number 105 had a total of 4 deviations but 3 persons were treated. The rate should then go down to 1.33.

Do I need to link the tables? How do I show all the rates in each quarter for each site number from PRPOV?

PRPOV table:

Site.Number	Subject.Name	PRSTDAT
102	102001	02/20/2025
102	102005	03/20/2025
102	102007	04/10/2025
105	105005	04/11/2024
105	105006	11/21/2024
105	105007	01/29/2025
105	105008	04/03/2025
105	105009	04/03/2025
105	105010	04/10/2025
105	105011	04/10/2025
105	105012	04/17/2025
105	105013	04/17/2025
106	106001	01/10/2024
106	106002	01/03/2024
106	106003	12/13/2023
106	106004	01/31/2024
106	106006	01/22/2025
106	106007	01/22/2025
106	106008	04/09/2025
106	106009	03/18/2025
107	107001	11/09/2023
107	107003	12/01/2023

Deviation Table:

Site.Number	Subject.Name	DVSTDAT
102	102003	03/21/2025
105	105002	04/08/2024
105	105003	04/08/2024
105	105005	05/31/2024
105	105005	03/03/2025
106	106003	02/02/2024
107	107001	11/29/2023
112	112002	03/30/2025
112	112004	01/28/2025
121	121006	02/12/2025

[AC422]

Hi @Greg_Deckler, the deviation table has 3 under site number 105 in 2024 Q2 but the PRPOV table has only 1 under site number 105 in 2024 Q2. The rate should then be 3/1 = 3.

Here's what I want to see as the end result (showing only site numbers 102, 105 and 106 for now):

Quarter	Site Number	Deviation Rate
2024 Q2	102	0.00
2024 Q2	105	3.00
2024 Q2	106	0.25
2024 Q3	102	0.00
2024 Q3	105	3.00
2024 Q3	106	0.25
2024 Q4	102	0.00
2024 Q4	105	1.50
2024 Q4	106	0.25
2025 Q1	102	0.50
2025 Q1	105	1.33
2025 Q1	106	0.14
2025 Q2	102	0.33
2025 Q2	105	0.44
2025 Q2	106	0.13

[Greg_Deckler · Accepted solution]

@AC422 I created a YQ table and a Sites table and this measure. See PBIX attached below sig:

Measure = 
    DIVIDE( 
        COUNTROWS( DISTINCT('Deviation'[Subject.Name]) ),
        COUNTROWS( DISTINCT( 'PRPOV'[Subject.Name] ) )
    )

[Greg_Deckler]

@AC422 I don't understand the calculation here. From what I see in Deviation table, in Q2 2024 there are three different subject names, 105002, 105003, 105005. So that would seem like 3 and 3 which would be 1. 

Also, can you share what you are trying to achieve (the end result).