How to make formula of total step yield? (measure which is using the result of another measure)

[OSAMU]

Dear Community,

Need help from DAX experts!

I am strugged in DAX for calculating total step yield of processed product.

step n yield = sum(qty out@step n) / sum(qty in@step n)

total step yield = step1 yield x step2 yield x ... x stepN yield, or exp(sum(ln(step n yield)))

all of yield metircs have to be calcuated dynamically based on how user filters.

By product, by day, by any...

How can I make it happen???

Date	Product	Step	in qty	out qty
1-Jan	P1	S1	100	99
1-Jan	P1	S2	110	105
1-Jan	P2	S1	50	40
1-Jan	P2	S2	60	30
2-Jan	P1	S1	50	50
2-Jan	P2	S1	100	100
2-Jan	P2	S2	80	75
2-Jan	P2	S3	90	50

measure

step yield = divide(sum(out qty),sum(in qty))

Regards,

Osamu

P.S. I don't have permission to upload sample pbix file...

[v-juanli-msft · Accepted solution]

Hi @OSAMU 

Create measures

Measure = CALCULATE(SUM(Sheet2[in]),FILTER(ALLSELECTED(Sheet2),Sheet2[step]=MAX(Sheet2[step])))

Measure 2 = CALCULATE(SUM(Sheet2[out]),FILTER(ALLSELECTED(Sheet2),Sheet2[step]=MAX(Sheet2[step])))

Measure 3 = [Measure 2]/[Measure]

Measure 4 = PRODUCTX(VALUES(Sheet2[step]),[Measure 3])

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OSAMU]

Dear Maggie,

How wonderful this measure is!!!

You solved my headache for last seveal months.

Thank you,

Osamu

[OSAMU]

Dear Magge and experts

One more help I need.

Your solution is working fine for showing single value view like a CARD.

How can I extend your solution to show the total step yield value by date hierarchy (such as yield trend by date/week/month/quarter by line chart)? 

Because measure formula ignores date, and the total step yield value by date hierarchy is shown wrongly.

Best regards,

Osamu