Urgent Help !!!Report view for with condition - Weekly

[619SK]

My task is to add "point" column based upon sample data.  have added sample data along with condition.

1) How to calculate point based upon condition  and add in report view with weeks filter

2) How to show particular Age category count 0 as when i added week filter then it shows only those who are having value

Points are calculated based upon conditions. For example - Age category ">180 days" defect count is 3 & as per condition it should so "0" as point also for "90-180 Days" there is 0 count of defect hence as per condition 100 point.

1) Outcome view from Bi

Age Category	Open Defect Count	Points	RAG
>180 Days	3	0	Red
90-180 Days	0	100	Green
0-15 Days	53	80	Green
15-30 Days	3	100	Green
60-90 Days	0	100	Green
30-60 Days	2	90	Green

Condition for point calculations

Sample data

Start Date	Defect Status	Start of Week	Age	Age Category
16-11-2023	Open	13-11-2023	42	30-60 Days
20-11-2023	Open	20-11-2023	38	30-60 Days
29-11-2023	Open	27-11-2023	29	15-30 Days
07-12-2023	Open	04-12-2023	21	15-30 Days
10-12-2023	Open	04-12-2023	18	15-30 Days
16-12-2023	Open	11-12-2023	12	0-15 Days
18-12-2023	Open	18-12-2023	10	0-15 Days
18-12-2023	Open	18-12-2023	10	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
19-12-2023	Open	18-12-2023	9	0-15 Days
21-12-2023	Open	18-12-2023	7	0-15 Days

[Ashish_Mathur]

Hi,

Why should the answer be 80 for 0-15 days and 53?  There is no such entry in the Points calculation table.

[619SK]

Hi, 56 is total count of Defect for 0-15 days & 80 is Point as per the condition as its >10

[Anonymous]

Hi @619SK ,

Please  try:
1. Unpivot these columns:

2. then please create 2 measures:

Open Defect Count = COUNTROWS('Table')

Points = 
VAR _cur_category = SELECTEDVALUE('Table'[Age Category])
VAR _count = [Open Defect Count]
VAR _points = CALCULATE(SUM('ConditionTable'[Value]),'ConditionTable'[Attribute]=_cur_category && 'ConditionTable'[Count of Defect]=_count)
RETURN
_points

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[619SK]

If i add Filter with Status (Open/Close) then it shows only Age category which is having values.

How can i show Age category  as 0  and Point 100.

[Anonymous]

Hi @619SK ,

Please try:

Points = 
VAR _cur_category = SELECTEDVALUE('Table'[Age Category])
VAR _count = [Open Defect Count]
VAR _points = CALCULATE(SUM('ConditionTable'[Value]),'ConditionTable'[Attribute]=_cur_category && 'ConditionTable'[Count of Defect]=_count) + 0
RETURN
_points

And

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[619SK]

Thanks @Anonymous  I will try