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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zairee
Frequent Visitor

How to calculate fault rate from 2 table with condition

Dear all, I have 2 table data 

 

TABLE 1   
DEFECTLOCATIONMonthYEAR
BRAND A1JAN2019
BRAND B1JAN2019
BRAND C1JAN2019
BRAND A2JAN2019
BRAND B2JAN2019
BRAND C2JAN2019
BRAND A1JAN2019
BRAND A2FEB2019
BRAND C1FEB2019
BRAND A1FEB2019
BRAND B1FEB2019
BRAND C1FEB2019
BRAND A2FEB2019
BRAND B2FEB2019
BRAND C2FEB2019
BRAND A1FEB2019
BRAND A2FEB2019
BRAND C1FEB2019

 

Table 2   
BrandTotal of BrandMonthYear
BRAND A121FEB2019
BRAND B151FEB2019
BRAND C93FEB2019
BRAND A117JAN2019
BRAND B145JAN2019
BRAND C92JAN2019

 

OUTPUT 1

LOCATION 1 & 2DEFECTDEFETTOTAL OF BRANDTOTAL OF BRANDFAULT RATEFAULT RATE
LOCATION 1 & 2JANFEBJANFEBJANFEB
BRAND A351171212.56%4.13%
BRAND B221451511.38%1.32%
BRAND C2492932.17%4.30%

 

OUTPUT 2

LOCATION 1DEFECTDEFETTOTAL OF BRANDTOTAL OF BRANDFAULT RATEFAULT RATE
LOCATION 1JANFEBJANFEBJANFEB
BRAND A221171211.71%1.65%
BRAND B111451510.69%0.66%
BRAND C1392931.09%3.23%


Expected result as output 1 and output 2.

Defect sum of defect in table 1 and total of ne base on brand and month.

kindly assist me to calculate fault rate base on above condition

Direct formula to calculate fault rate = DEFECT/TOTAL OF BRAND

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @zairee ,

 

We can create measures as below to get the output1 table.

 

defect Feb = CALCULATE(COUNT(Table1[DEFECT]),FILTER(Table1,Table1[Month]="FEB"))
defect Jan = CALCULATE(COUNT(Table1[DEFECT]),FILTER(Table1,Table1[Month]="JAN"))
TOTAL OF BRAND FEB = CALCULATE(SUM(Table2[Total of Brand]),FILTER(Table2,Table2[Brand]= MAX(Table1[DEFECT]) && Table2[Month]="FEB"))
TOTAL OF BRAND JAN = CALCULATE(SUM(Table2[Total of Brand]),FILTER(Table2,Table2[Brand]= MAX(Table1[DEFECT]) && Table2[Month]="JAN"))
FAULT RATE FEB = [defect Feb]/[TOTAL OF BRAND FEB]
FAULT RATE JAN = [defect Jan]/[TOTAL OF BRAND JAN]

For the output2, we can add Location column in slicer to filter the table visual and edit the interations as below.

 

Capture.PNG

 

Reagrds,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @zairee ,

 

We can create measures as below to get the output1 table.

 

defect Feb = CALCULATE(COUNT(Table1[DEFECT]),FILTER(Table1,Table1[Month]="FEB"))
defect Jan = CALCULATE(COUNT(Table1[DEFECT]),FILTER(Table1,Table1[Month]="JAN"))
TOTAL OF BRAND FEB = CALCULATE(SUM(Table2[Total of Brand]),FILTER(Table2,Table2[Brand]= MAX(Table1[DEFECT]) && Table2[Month]="FEB"))
TOTAL OF BRAND JAN = CALCULATE(SUM(Table2[Total of Brand]),FILTER(Table2,Table2[Brand]= MAX(Table1[DEFECT]) && Table2[Month]="JAN"))
FAULT RATE FEB = [defect Feb]/[TOTAL OF BRAND FEB]
FAULT RATE JAN = [defect Jan]/[TOTAL OF BRAND JAN]

For the output2, we can add Location column in slicer to filter the table visual and edit the interations as below.

 

Capture.PNG

 

Reagrds,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi frank..it work..but can we change the formula from fix month to variable value. so we can choose the fault rate in the month by slicer.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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