Forum Discussion

619SK's avatar
619SK
Helper II
2 years ago

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

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 CategoryOpen Defect CountPointsRAG
>180 Days30Red
90-180 Days0100Green
0-15 Days5380Green
15-30 Days3100Green
60-90 Days0100Green
30-60 Days290Green

 

 

 

Condition for point calculations

 

Sample data

Start DateDefect StatusStart of WeekAgeAge Category
16-11-2023Open13-11-20234230-60 Days
20-11-2023Open20-11-20233830-60 Days
29-11-2023Open27-11-20232915-30 Days
07-12-2023Open04-12-20232115-30 Days
10-12-2023Open04-12-20231815-30 Days
16-12-2023Open11-12-2023120-15 Days
18-12-2023Open18-12-2023100-15 Days
18-12-2023Open18-12-2023100-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-202390-15 Days
21-12-2023Open18-12-20237

0-15 Days

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      619SK
      Helper II
      2 years ago

      Thanks Anonymous  I will try 

    • 619SK's avatar
      619SK
      Helper II
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

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

    • 619SK's avatar
      619SK
      Helper II
      2 years ago

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