Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |