Product and Average

[vincentakatoh]

Hi, 

Objective is to create below matrix in PBI. Need help with level 3 calculation.

Level 3, Avg Yield: Need help!

Level 2, Roll Yield: Achieve using New Table + Summarize column

Level 1, Yield: Achieve using measure

Fomula in Excel (Column J)

Sample Data (Actual data has >100k rows, multiple customers, families and stations)

Customer	Family	Station	PassCount	TotalCount
Class	Group1	sub-grp1	10	15
Class	Group1	sub-grp2	5	12
Class	Group2	sub-grp3	6	18
Class	Group2	sub-grp4	7	13

Sample data

[vik0810]

Hi, I created a PBI file with your data and uploaded it here. The end result looks like this:

[vincentakatoh]

@vik0810, 

Did you upload the correct file? Downloaded the file but the Roll and Avg yield columns are missing. 

rgds, vincent

[vik0810]

@vincentakatoh, you are right, sorry, i uploaded new file

[vincentakatoh]

hi @vik0810, 

Thanks. I really like your solution.

Nontheless, got below error msg when I put "PassCountM" and "TotalCountM" in a matrix using actual data. 

FirstPassQtyM = IF(HASONEVALUE(data2[Station]), VALUES(Data2[FirstPassQty]))
InputFrM = IF(HASONEVALUE(data2[Station]), VALUES(Data2[InputFr]))

Please help!

[vik0810]

It looks like you have different FirstPassQty and InputFr values for the station. So the VALUES Function doesn't return single values.

I guess your measures should look like

FirstPassQtyM = SUM(Data2[FirstPassQty])

InputFrM = SUM(Data2[InputFr])

[vincentakatoh]

Hi @vik0810, 

Uploaded my sample dax. Can advise how to correct measure, "Error3_RollYield"

Objective is for "Error3_RollYield" = "RollYield_MyGoal". Tried all day w/o success. 

https://1drv.ms/u/s!ArjVwEnHONXNggEon7i3aTQQgX4-

[vik0810]

Hi @vincentakatoh, the file isn't available. have you resolved your issue in the meantime? 

[vincentakatoh]

hi @vik0810, 

Thanks again. Yes, managed to resolved issue with help from this forum. 

Also all thanks to your reply and sample Pbi file. Help to solve other problems too. Thanks thanks. 

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

[Anonymous]

Hi @vincentakatoh,

Have you tried to use "Quick Measures" here you could for example make a measure where you simply click on multiplication and select the columns you want to calculate. After that you can calculate an average by using the AVERAGE function. or you can create it through a "Quick Measure" again.

I was'nt entirely sure what you wanted to achieve so if I answered the wrong question please let me know 🙂

Regards,

L.Meijdam