cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zairee Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: How to calculate fault rate from 2 table with condition

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 other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: How to calculate fault rate from 2 table with condition

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 other members find it more quickly.
Highlighted
zairee Frequent Visitor
Frequent Visitor

Re: How to calculate fault rate from 2 table with condition

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 376 members 3,603 guests
Please welcome our newest community members: