Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |