Datetable and Summarize Column

[vincentakatoh]

Hi, 

Used summarise column to calculate roll yield (= A*B*C). Also created a Datetable. 

Que: Any way to re-write dax such that roll yield does not apply when I drill up from date to week? Currently roll yield applies for station to product and date to week. 

RollYieldFam = 
    SUMMARIZECOLUMNS(
		'Data2'[Customer],
		'Data2'[Family],
    	'Data2'[station],		
		'Data2'[TestDate],
		"OutputFr",sum(Data2[OutputFr]),
		"FrPassCount",sum(Data2[FirstPassQty]),
        "FrFailCount",sum(Data2[FirstFailQty]),
		"InputFr",SUMX(data2, [FirstFailQty]+Data2[FirstPassQty]),
        "YieldFr",DIVIDE(sum(Data2[FirstPassQty]),sum(Data2[InputFr]),1)
        	)

Yield_Roll = CALCULATE(PRODUCT([YieldFr]))

[vincentakatoh]

Hi, 

Provided Sample DAX in below link. Hopefully this can explain my problem better. Please help!

https://1drv.ms/u/s!ArjVwEnHONXNggSTw6hzNhK3NzjV

[v-yulgu-msft]

Hi @vincentakatoh,

Below measure can return correct percentage (CountPass/Totalcount) per level.

Measure Yield = DIVIDE(SUM(Yield[CountPass]),SUM(Yield[TotalCount]),1)

   

But, based on my test, it seems that PRODUCT function always evaluates the product value for each detailed row in whole table. So, when we drill up to weekly level, it returns wrong result.

If you work it out later, appreciate that you could share your solution here.

Best regards,
Yuliana Gu

[vincentakatoh · Accepted solution]

Hi @v-yulgu-msft, 

Thanks. Solved problem with help from @v-jiascu-msft. or rather work around the problem by not using summarizecolumns. 

Sample file attached. Appreciate reply from both again. 

https://1drv.ms/u/s!ArjVwEnHONXNggfW2ETtdFOTf9Zx