Calculate rate from two tables

[jadewind]

Hi all,

I'm new to Power BI. I have got a few tables in Power BI and try to link them correctly and do some basic routine calculations. 

Now I have two tables, one lists Incidents data and another lists Number of staff data. Both of them have fields like Region, Cost Centre, Financial Year etc. I also have a third table that contains cost centre hierarchy if needed, to link these two tables by cost centre codes. 

In Incidents data, I can count how many incidents by Region by Financial Year. In staff data, I can count how many staff by Region by Financial Year.

Now I need to calculate the incident rate = No. of Incidents / Number of Staff. 

I need to also be able to break down the incident rate by Region, Cost Centre etc AND by Financial Year. 

I find the rates I get are not correct. My DAX is "

Incident Rate = COUNTA(Incidents[Incident No.]) / SUM(Staffs[Total]) * DISTINCTCOUNT(Staff[Report Date])

"

How should I do it to make it work?

Thanks in advance for any help!

[v-sihou-msft · Accepted solution]

@jadewind

In this scenario, since both table contains Region, Cost Centre, Financial Year fields, you suppose already built the relationship between two tables. To calcualte the rate, you just need to use ALLEXCEPT() to have your calculation group on corresponding fields to get correct result.

Incident Rate =CALCULATE(COUNTA(Incidents[Incident No.]),ALLEXCEPT(Incidents,Incidents[Region],Incidents[Cost Centre],Incidents[Financial Year])) /

(CALCULATE(SUM(Staffs[Total]), ALLEXCEPT(Staffs,Staffs[Region]),Staffs[Cost Centre],Staffs[Financial Year]))

*

CALCULATE(DISTINCTCOUNT(Staff[Report Date]),ALLEXCEPT(Staffs,Staffs[Region]),Staffs[Cost Centre],Staffs[Financial Year])))

Regards,