cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate fault rate from 2 table with condition

Dear all, I have 2 table data

 TABLE 1 DEFECT LOCATION Month YEAR BRAND A 1 JAN 2019 BRAND B 1 JAN 2019 BRAND C 1 JAN 2019 BRAND A 2 JAN 2019 BRAND B 2 JAN 2019 BRAND C 2 JAN 2019 BRAND A 1 JAN 2019 BRAND A 2 FEB 2019 BRAND C 1 FEB 2019 BRAND A 1 FEB 2019 BRAND B 1 FEB 2019 BRAND C 1 FEB 2019 BRAND A 2 FEB 2019 BRAND B 2 FEB 2019 BRAND C 2 FEB 2019 BRAND A 1 FEB 2019 BRAND A 2 FEB 2019 BRAND C 1 FEB 2019

 Table 2 Brand Total of Brand Month Year BRAND A 121 FEB 2019 BRAND B 151 FEB 2019 BRAND C 93 FEB 2019 BRAND A 117 JAN 2019 BRAND B 145 JAN 2019 BRAND C 92 JAN 2019

OUTPUT 1

 LOCATION 1 & 2 DEFECT DEFET TOTAL OF BRAND TOTAL OF BRAND FAULT RATE FAULT RATE LOCATION 1 & 2 JAN FEB JAN FEB JAN FEB BRAND A 3 5 117 121 2.56% 4.13% BRAND B 2 2 145 151 1.38% 1.32% BRAND C 2 4 92 93 2.17% 4.30%

OUTPUT 2

 LOCATION 1 DEFECT DEFET TOTAL OF BRAND TOTAL OF BRAND FAULT RATE FAULT RATE LOCATION 1 JAN FEB JAN FEB JAN FEB BRAND A 2 2 117 121 1.71% 1.65% BRAND B 1 1 145 151 0.69% 0.66% BRAND C 1 3 92 93 1.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

## 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.

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

## 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.

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
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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 376 members 3,603 guests
Recent signins: