Calculate crime rates in Power BI

[Pedro_Martínez]

Dear PBI Community,

I'm pretty now to Power BI and I'm trying to calculate the crime rate per 100.000 inhabitants per municipality. I have a table with more than 100k rows, each one corresponding to a singular crime.

Año	Horario	Cod_Departamento	Cod_Municipio	Cod_Zona	Sexo	Edad	Cod_delito	Grupo_delito	Índice
2016	1	1	101	12	1	56	69	3	1
2016	1	6	602	99	1	42	69	3	2
2016	1	2	204	99	1	38	69	3	3
2016	2	1	105	99	1	69	69	3	4
2016	3	1	101	6	1	27	69	3	5
2016	2	1	108	99	1	39	75	3	6
2016	2	1	108	99	1	39	75	3	7
2016	2	1	108	99	1	39	75	3	8
2016	1	5	511	99	1	25	75	3	9
2016	2	5	506	99	1	36	69	3	10
2016	4	11	1101	99	1	27	75	3	11
2016	2	13	1318	99	1	44	75	3	12
2016	2	14	1401	99	1	23	72	3	13
2016	1	4	409	99	1	51	75	3	14
2016	3	1	106	99	1	30	69	3	15

In addition, I have established a relationship with another table with the code of each municipality and a column for the population of each one. Cod_Municipio are the columns related.

Cod_Municipio	Población
101	38103
102	1711
103	1329
104	88
105	971
106	1430
107	1164
108	9788
109	244
110	2132

So I need the rate per 100k inhabitants of each particular type of crime -categorized with numers from 1 to 7 in the Grupo_Delito column- and each municipality (Cod_Municipio). The idea is that after the measure, the visuals interact with other variables like age or sex.

I would appreciate very much your help.

Kind regards!

[daXtreme]

// Such a measure should work for
// any slicing and dicing.
// Here's the correct code:

[Rate Per 100K] =
// First, get the number of crimes in
// the current context. 'Crimes Statistics'
// is the fact table with individual crimes.
var CrimeCount = COUNTROWS( 'Crimes Statistics' )
// Then, you've got to sum up the populations
// from the Population table for all the cities
// that are visible in the current context. I assume
// that when you're slicing by city, it's done
// through the Population table. it is important
// to always slice by dimensions, never by the raw
// fact table's columns.
var TotalPopulation = SUM( Population[Población] )
var RatePer100K = 
    DIVIDE(
        CrimeCount * POWER(10, 5),
        TotalPopulation
    )
return
    RatePer100K

// This measure correctly responds to any slicing
// and dicing.

[rohit_singh · Accepted solution]

Hi @Pedro_Martínez ,

Please try this : 

1) Using the relationship between the two tables, add a column on the crime rates table to show the population

Pop = RELATED(Population[Población])

2) Create a measure to compute crime rate

Crime Rate / 100k =

var _crimes = countrows(CrimeRates)

var _pop = max(CrimeRates[Pop])

var _crime100k = DIVIDE(_crimes,_pop,0) * 100000

RETURN

IF (ISBLANK(_crime100k), BLANK(), _crime100k)

3) This is the final result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂