Power BI Report view - Conditional calculation

[619SK]

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**

@jennratten 

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

[zenisekd]

@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.