Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
619SK
Helper II
Helper II

Power BI Report view - Conditional calculation

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

619SK_0-1703720659130.png

619SK_1-1703720796276.png

@jennratten 

 

Sample Data (Count taken for explanation)

Start DateDefect StatusStart of WeekAgeAge Category
18-12-2023Open18-12-20231090-180 Days
18-12-2023Open18-12-2023100-15 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Close18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023915-30 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-2023960-90 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023960-90 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-2023960-90 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023930-60 Days
19-12-2023Open18-12-2023915-30 Days
19-12-2023Open18-12-2023930-60 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-202390-15 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023930-60 Days
19-12-2023Open18-12-20239>180 Days
19-12-2023Open18-12-2023915-30 Days
19-12-2023Open18-12-2023915-30 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Close18-12-20239>180 Days
19-12-2023Close18-12-20239>180 Days
19-12-2023Close18-12-20239>180 Days
19-12-2023Close18-12-20239>180 Days
19-12-2023Open18-12-2023990-180 Days
19-12-2023Open18-12-20239>180 Days
21-12-2023Open18-12-202370-15 Days
1 REPLY 1
zenisekd
Super User
Super User

@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 

zenisekd_0-1703765804262.png

 

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors