Need help to calculate defect rate over months

[nakul555]

I have 2 unrelated queries pulled from oracle as shown below. I need to calculate defect rate over months.

Each row in table one depicts one single defect on that date. So i would need (sum of all defects in a month)/(total sales in a month)  in a line chart over months. Please help.

Table 1
Unique identifier	Date
5982	15/8/2019
5842	16/8/2019
6665	17/8/2019
7789	18/8/2019
5632	2/9/2019
1235	3/9/2019
7536	4/9/2019
7542	5/9/2019
3698	6/9/2019
5542	7/9/2019
2295	8/9/2019
9658	9/9/2019

Table 2
Months	Sales
Jun-19	6000
Jul-19	7000
Aug-19	8000
Sep-19	6000
Oct-19	8000
Nov-19	9000
Dec-19	7000

[v-jayw-msft · Accepted solution]

Hi @nakul555 ,

Check the measure.

Measure = CALCULATE(SUM('Table'[Unique identifier]),FILTER('Table',FORMAT('Table'[Date],"YYYYMM")=FORMAT(SELECTEDVALUE('Table (2)'[Months]),"YYYYMM")))/SELECTEDVALUE('Table (2)'[Sales])

Result would be shown as below.

 

Best Regards,

Jay

[amitchandak]

@nakul555 , In the second table, create a date like this (based on the given format)

Date = "01-" & [Months] // Change data type to date.

Now create a common date table with months and join to date in both tables and use month from date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

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

[nakul555]

How do I code the formula for calculating defect rate?

[amitchandak]

@nakul555 , Try measure like

divide(count(Table[Unique identifier]), sum(Table2[Sales]]))

[nakul555]

@amitchandak  Tried that, but its returning 0. I created a relationship between all months from 3 tables. Is that correct?