The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm adding sample data for weekly report of Defects Points
Here i want calculate Actual Point based upon condition depends upon count of Open defects for particular age category. i have added condition in excel.
Example
if Defect Count for Age Category "15-30 Days" is 9 then Point is 80. Also if count is 0 (No Defect) for particular age category then Point is 100.
I'm not able to calculate "Points" column
**Weekly Report**
Sample Data (Count taken for explanation)
Start Date | Defect Status | Start of Week | Age | Age Category |
18-12-2023 | Open | 18-12-2023 | 10 | 90-180 Days |
18-12-2023 | Open | 18-12-2023 | 10 | 0-15 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Close | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 15-30 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 60-90 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 0-15 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 60-90 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 60-90 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 30-60 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 15-30 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 30-60 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 0-15 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 30-60 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 15-30 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 15-30 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Close | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Close | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Close | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Close | 18-12-2023 | 9 | >180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | 90-180 Days |
19-12-2023 | Open | 18-12-2023 | 9 | >180 Days |
21-12-2023 | Open | 18-12-2023 | 7 | 0-15 Days |
@619SK I would not go the way of calculation / conditions, it seems way too complicated...
My suggestion is to:
1. in sample query filter out the close status and then group the sample data in power query, based on the "age category" and count the cases.. You will get table with age category and count of cases.
2. add a new conditional column "New Count of points with max of 10", which will keep the original number of cases, if 10 or less. If >10, then insert 10. So the highest number in the columns is 10. This will allow you to merge the points table with sample.
3. Transform the points table
4. Marge the points table to the sample based on two columns - "age category" and "New Count of points with max of 10". Next, expand the points in the sample table.
Job done. the disadvantage is obviously that you wont be able to drill through etc. Another way is to add a fairly long and complicated SWITCH function where you will have to define a lot of conditions.