Forum Discussion
Measure multply rows in matrix
Hi,
New to Power BI. Will like to create a new measure to calculate yield rate (%)
1) Line 1 Yield = S1 Yield * S2 Yield
2) Station Yield = Count P/(CountP+ CountF)*100%
Question: How to do you write the DAX equivalent for above.
| Area | Line | station | test results |
| SMT | Line1 | S1 | P |
| SMT | Line1 | S1 | F |
| SMT | Line1 | S1 | P |
| SMT | Line1 | S2 | F |
| SMT | Line1 | S2 | F |
| SMT | Line1 | S2 | P |
Hi vincentakatoh,
Firstly, duplicate your table in Query Editor, then pivot column and add a index column for this new table.
Secondly, create new columns as follows.
Yieldpertestresult = 'newtable'[S1]/('newtable'[S1]+'newtable'[S2]+'newtable'[S4])
Lineyield = IF('newtable'[Line]=LOOKUPVALUE('newtable'[Line],'newtable'[Index],'newtable'[Index]-1),'newtable'[Yieldpertestresult]*LOOKUPVALUE('newtable'[Yieldpertestresult],'newtable'[Index],'newtable'[Index]-1),0)Thirdly, create a table visual as shown in the following screenshot.
Thanks,
Lydia Zhang
9 Replies
I do not see anything numeric in your data that would allow you to multiply 2 things together. I'm assuming that CountP would be the count of all things with test results P and similar CountF. Those you could do with something like:
CountP = CALCULATE(COUNTA([Line]),FILTER(table,[test results]="P")) CountF = CALCULATE(COUNTA([Line]),FILTER(table,[test results]="F"))
Greg_Deckler thanks for the prompt reply.
In my example
Station 1 yield = 2(P+P)/3(P+F+P)= 66%
Station 2 yield = 1 (P)/3 (F+F+P)= 33%
Line 1 yield = Station 1 Yield x Station 2 Yield =66% * 33% = 0.21%
I have added 3 measure
- Count P = COUNTROWS(FILTER('Data', [TestResult] = ("P")))
- Count F = COUNTROWS(FILTER('Data', [TestResult] = ("P")))
- Yield = [CountP/([CountP+[CountF)]
Que: How do I write a DAX to calculate the Line 1 Yield?
Station 1 CountP CountF Yield S1 2 1 67% S1 1 2 33% Line1 22% To add,
Power BI currently calculates Line 1 yield as below, summing all CountP and CountF
Station 1 CountP CountF Yield Line1 3 3 50% The Line1 yield i'm trying to calculate = Line 1 yield = Station 1 Yield x Station 2 Yield =67% * 33%= 22%
Station1 Yield Station2 Yield Line 1 Yield Line1 67% 33% 22% Thanks.
