Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Crime Rate Calculation Help!

I am trying to figure out how to calculate crime rates within the Power BI Desktop app. 

 

In Excel, it would look like: (Total Amount of Crime ÷ Population * 1,000)

 

I currently have the following variables:

  • CRID (when using distinct count, this provides a total amount of crime)
  • Location (3 different cities)
  • Population (Split up into 9 variables by city and by year)
  • Year (2016-2020)

 

What I really need is a way to get crime rates for each city and each year for a total of 9 individual crime rates

 

(e.g. CRID ÷ Location Population for Specific Year * 1,000)

 

Is there a way to do this??

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

    It would be like

    divide(sum(Table1[Total Amount of Crime]),Sum(Table2[Population)) * 1000

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    5 years ago

    Hi Anonymous 

     

    If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

    If not, please kindly elaborate more.