Measure to Calculate the Defect Rate Not Obtaining the Correct Answer

[Amos_Lim]

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 

[Ashish_Mathur]

Hi,

Show the expected result very very clearly.

[Amos_Lim]

Below is the expected results.

[Ashish_Mathur]

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

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

[Amos_Lim]

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.

[Ashish_Mathur]

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

[Amos_Lim]

Thanks, Ashish_Mathur.

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

Power BI Sample File & Excel File 

[Ashish_Mathur]

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.

[Amos_Lim]

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 

[Ashish_Mathur]

Hi,

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

[Amos_Lim]

Excel file as follow:-

Sample Excel File 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Amos_Lim]

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

[Ashish_Mathur]

You are welcome.

[Amos_Lim]

Thanks, Ashish_Mathur.

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

[Amos_Lim]

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

[Amos_Lim]

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.