Measure multply rows in matrix

[vincentakatoh]

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

[Greg_Deckler]

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"))

[vincentakatoh]

@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%

[vincentakatoh]

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. 

[Anonymous]

Hi @vincentakatoh,

In your scenario, create the another three measures(replace Table2 with you own table) and Matrix visual.

Station1 Yield = CALCULATE([Yield],FILTER(Table2,Table2[station]="S1"))
Station2 Yield = CALCULATE([Yield],FILTER(Table2,Table2[station]="S2"))
Line 1 Yield = [Station1 Yield]*[Station2 Yield]

Thanks,
Lydia Zhang

[vincentakatoh]

Hi Lydia, 

Thanks. 

The actual data has thousand of lines. There are many multiple lines and multiple stations (unknow number). 

Is there a DAX formula that I can use to calculate the station and line yield dynamically based on filter. 

PS. Love this forum and the amazingly prompt answer. Great for Power BI dummy like me. 

[vincentakatoh]

Added more rows to better reflect actual data

1) Staiton yield = CountP/(CountP+CountF)

2) Line yield = station yield * station yield

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
SMT	Line2	S1	P
SMT	Line2	S1	F
SMT	Line2	S1	P
SMT	Line2	S2	F
SMT	Line2	S2	F
SMT	Line2	S2	P
SMT	Line3	S1	P
SMT	Line3	S1	F
SMT	Line3	S1	P
SMT	Line3	S4	F
SMT	Line3	S4	F
SMT	Line3	S4	P

[Anonymous · Accepted solution]

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

[vincentakatoh]

Hi Lydia, 

Thanks and appreciate the patience.

1) I encountered below error in 2nd step, add column for "yieldpertestresults". 

2) Below requires "S1, S2, S4" to be written. But in actual data, there are many station names (S1, S2, S3... S50). Anyway to use "station" vs "S1, S2, S4... " in the formula. 

Yieldpertestresult = 'newtable'[S1]/('newtable'[S1]+'newtable'[S2]+'newtable'[S4])

3) The data is also retrieve from a folder with mutilple excel sheets. One sheet for a different date. 

[vincentakatoh]

Hi Lydia, 

Thanks! Managed to calculate line yield. Was adding column in Edit Query. Should have done it outside. 

Question: Below requires "S1, S2, S4" to be written. But in actual data, there are many station names (S1, S2, S3... S50). Anyway to use "station" vs "S1, S2, S4... " in the formula. 

Yieldpertestresult = 'newtable'[S1]/('newtable'[S1]+'newtable'[S2]+'newtable'[S4])