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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Amos_Lim
Resolver I
Resolver I

Measure to Calculate the Defect Rate Not Obtaining the Correct Answer

I created two measures and used them to calculate the failure rate per 100,000 but did not receive the desired result.

 

The first measure was used to calculate the overall number of defects depending on bike number.

Total Defect By Bike No = COUNTROWS(FILTER('Data', 'Data'[Bike] = MAX(Data[Bike])))

 

The second measure was used to compute the defect rate based on 100,000 KM traveled.

Defect Rate per bike = [Total Defect By Bike No] / CALCULATE(SUM(Mileage[Mileage]), ALLEXCEPT(Mileage, Mileage[Bike]))*100000

 

The first measure was correct, but when it came to defect rate per bike, it used the total mileage of all bikes rather to a specific bike number to compute

The logic to the second measure seem correct but I am not sure where goes wrong.

 

Help would appreciate and thank you.

Please find attached the Excel sample file and the pibx file.

 

Sample Files 

 

1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1711247947819.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

Show the expected result very very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Below is the expected results.

 

Table.JPG

Assuming Total Defect and Total Mileage are measures that you have already written, write this measure

Measure = divide([Total defect],[Total mileage])*10000


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I have a measure for total fault (first measure in post 1), but I don't have one for total mileage because my mileage table breaks it down by bike and year/month kilometers.

 

That is why I am utilizing the second measure to total the mileage based on the bike.

 

If I want to see the breakdown details, I will be able to select the slicers for year/quarter/bike number from my Dashboard.

 

 

I'll need to see the file to offer any help.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, Ashish_Mathur.

I tried the filter, divide but did not receive the desired results.

 

Power BI Sample File & Excel File 

 

I am lost in that PBI file.  Why are there so many visuals there.  Clearly just show one Table visual and show the expected result via a text box explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

There were only two charts I intended to display.

The first one was the defect rate by bike number.

The second one was the defect rate per subsystem.

 

I removed the checker page since I use it to analyze my results.

 

For the first chart, for e.g. bike 115 should have the result of 60.467 instead of 8.0178. (refer to message 4)

 

For the second chart, e.g. subsystem AATS in year 2022 & 2023 total defect was 32 & 27.

and the total mileage (all bikes included) for year 2022 & 2023 was 718051 & 666353.

Hence, the defect rate should be 0.4456 (2022 AATS defect / 2022 total mileage *100000) & 0.4051 (2023 AATS defect / 2023 total mileage *100000) but I am getting the result as in the chart  0.4333 & 0.5056.

 

Sample2 Power BI 

Hi,

Both those tables are coming from an MS Excel file.  Share the download link of that Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Excel file as follow:-

Sample Excel File 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1711247947819.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for your time and effort in assisting me. Thanks.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, Ashish_Mathur.

 

I saw you added an additional table called bike. Could you possibly share the Excel file with me as well?

Okay. I saw it was just the bike number. Thanks.

Amos_Lim
Resolver I
Resolver I

The "ALLEXCEPT" in

Defect Rate per bike = [Total Defect By Bike No] / CALCULATE(SUM(Mileage[Mileage]), ALLEXCEPT(Mileage, Mileage[Bike]))*100000

may have summed up all the bikes except the slected bike; how can it be reversed? i.e., only the bike in the slicer was selected to sum up the mileage.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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