Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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
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
@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.
How do I code the formula for calculating defect rate?
@amitchandak Tried that, but its returning 0. I created a relationship between all months from 3 tables. Is that correct?
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
53 |