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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
619SK
Helper I
Helper I

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors