Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Please, if any one can help it will be much appreciated. I have the following summarized table to calculate the over all yield, and i cannot see where my mistake is? I get the error at the bottom of the code: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
here is the code for the measure:
Solved! Go to Solution.
See if this works for you:
% Yield Val =
AVERAGE('Table'[% Yield])
Overall Yield =
AVERAGEX (
SUMMARIZE (
'Table',
'Dim Die'[DIE_TYPE],
'Dim Flow'[D flow],
'Dim Ft Product'[FT_PRODUCT],
'Dim Insertion'[D Insertion],
'Dim Lot'[LOTNO],
'Table'[Insertion Temp]
),
[% Yield Val]
)
Yield By Lot =
VAR Lot =
MAX ( 'Dim Lot'[LOTNO] )
RETURN
IF (
ISBLANK ( [% Yield Val] ),
BLANK (),
AVERAGEX (
FILTER ( ALL ( 'Table' ), RELATED ( 'Dim Lot'[LOTNO] ) = LOT ),
[% Yield Val]
)
)
Yield Change = [Overall Yield] - [Yield By Lot]
I've attached the sample BPBIX file
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
I was out for a few days, i tried your code yesterday, and it works.
Thank you very much
Regadrs,
Karim
The problem is that the code returns a table of values (SUMMARIZE returns a table). You can try:
OverAllYield1 =
AVERAGEX (
ADDCOLUMNS (
SUMMARIZE (
'ABU_Yield-Qty_Data1',
'ABU_Yield-Qty_Data1'[DIE_TYPE],
'ABU_Yield-Qty_Data1'[FT_PRODUCT],
'ABU_Yield-Qty_Data1'[Test Insertion],
'ABU_Yield-Qty_Data1'[Test Flow],
'ABU_Yield-Qty_Data1'[Insertion Temp]
),
"FT Final Pass Over All Yield Avg1",
CALCULATE (
AVERAGE ( 'ABU_Yield-Qty_Data1'[Yield (%)] ),
'ABU_Yield-Qty_Data1'[Test Flow] = "FT",
'ABU_Yield-Qty_Data1'[Test Insertion] = "Final Insertion"
)
),
[FT Final Pass Over All Yield Avg1]
)
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
thanks for the quick response, however, the i am still not able to get the correct yield average by grouping the data based on die type/FT product/Test insertion/test flow and insertion temp. Do you mind providing feedback on the logic of my measure? I am probably doing it wrong.
Thanks for your help
Karim
here is a sample data:
Desired output : yield change = overall yield - yield for each lot;
overall yield= average yield (per die/part/temp/testFlow/Test insertion) over time
sample data
DIE_TYPE | FT_PRODUCT | LOTNO | MEASURE_TIME | Yield (%) | Insertion Temp | Test Flow | Test Insertion |
die1 | part1 | 013A_lot | 5/9/2020 | 59.77 | Hot | FT | Fi_Insertion |
die1 | part1 | 013A_lot | 5/10/2020 | 97.54 | Room | FT | Fi_Insertion |
die1 | part1 | 013A_lot | 5/14/2020 | 89.52 | Cold | QA | Mi_Insertion |
die1 | part1 | 013A_lot | 5/10/2020 | 69.94 | Hot | QA | Mi_Insertion |
die1 | part1 | 013A_lot | 5/11/2020 | 87.47 | Room | QA | Mi_Insertion |
die1 | part3 | 013B_lot | 5/16/2021 | 83.14 | Room | FT | CA_Insertion |
die1 | part3 | 013B_lot | 5/15/2021 | 60.01 | Hot | FT | CA_Insertion |
die1 | part3 | 013B_lot | 5/17/2021 | 86.2 | Cold | FT | CA_Insertion |
die1 | part3 | 013B_lot | 5/15/2021 | 82.35 | Hot | FT | Fi_Insertion |
die1 | part3 | 013B_lot | 5/15/2021 | 70.22 | Hot | FT | Fi_Insertion |
die1 | part1 | 01BB_lot | 4/12/2019 | 95.91 | Room | FT | CA_Insertion |
die1 | part1 | 01BB_lot | 4/10/2019 | 67.55 | Hot | FT | CA_Insertion |
die1 | part1 | 01BB_lot | 4/22/2019 | 87.31 | Cold | FT | CA_Insertion |
die2 | part4 | 004A_lot | 5/24/2019 | 95.54 | Hot | FT | Fi_Insertion |
die2 | part4 | 004A_lot | 5/24/2019 | 89.93 | Hot | FT | Fi_Insertion |
die2 | part4 | 004A_lot | 5/26/2019 | 99.59 | Room | FT | Fi_Insertion |
die2 | part4 | 004A_lot | 5/26/2019 | 89.71 | Room | FT | Fi_Insertion |
die2 | part4 | 004A_lot | 5/28/2019 | 97.8 | Cold | FT | Fi_Insertion |
die2 | part4 | 004A_lot | 5/28/2019 | 94.53 | Cold | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/21/2019 | 89.88 | Room | FT | CA_Insertion |
die2 | part4 | 04AA_lot | 6/17/2019 | 85.15 | Hot | FT | CA_Insertion |
die2 | part4 | 04AA_lot | 6/23/2019 | 92.36 | Cold | FT | CA_Insertion |
die2 | part4 | 04AA_lot | 6/17/2019 | 94.06 | Hot | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/17/2019 | 89.17 | Hot | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/21/2019 | 90.88 | Room | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/21/2019 | 90.88 | Room | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/23/2019 | 98.46 | Cold | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/23/2019 | 92.52 | Cold | FT | Fi_Insertion |
die2 | part4 | 04AA_lot | 6/25/2019 | 93.37 | Cold | QA | Mi_Insertion |
die2 | part4 | 04AC_lot | 7/3/2019 | 83.47 | Cold | FT | CA_Insertion |
die2 | part4 | 04AC_lot | 7/2/2019 | 98.82 | Room | FT | Fi_Insertion |
die2 | part4 | 04AC_lot | 7/2/2019 | 90.4 | Room | FT | Fi_Insertion |
die2 | part4 | 04AC_lot | 7/3/2019 | 97.74 | Cold | FT | Fi_Insertion |
die2 | part4 | 04AC_lot | 7/3/2019 | 87.43 | Cold | FT | Fi_Insertion |
die2 | part4 | 04AC_lot | 7/4/2019 | 90.24 | Cold | QA | Mi_Insertion |
die2 | part2 | 004B_lot | 5/29/2019 | 93.38 | Hot | FT | Fi_Insertion |
die2 | part2 | 004B_lot | 5/31/2019 | 95.53 | Room | FT | Fi_Insertion |
die2 | part2 | 004B_lot | 5/31/2019 | 92.19 | Cold | FT | Fi_Insertion |
die2 | part2 | 004B_lot | 5/29/2019 | 97.33 | Hot | FT | Fi_Insertion |
die2 | part2 | 004B_lot | 5/31/2019 | 99.51 | Room | FT | Fi_Insertion |
die2 | part2 | 004B_lot | 5/31/2019 | 98.25 | Cold | FT | Fi_Insertion |
die2 | part2 | 04BA_lot | 6/7/2019 | 96.08 | Room | FT | CA_Insertion |
die2 | part2 | 04BA_lot | 6/9/2019 | 91.9 | Cold | FT | CA_Insertion |
die2 | part2 | 04BA_lot | 6/3/2019 | 92.39 | Hot | FT | Fi_Insertion |
die2 | part2 | 04BA_lot | 6/7/2019 | 96.17 | Room | FT | Fi_Insertion |
die5 | part2 | 029B_lot | 2/14/2022 | 94.4 | Hot | FT | CA_Insertion |
die5 | part2 | 029C_lot | 2/26/2022 | 97.47 | Hot | FT | Fi_Insertion |
die5 | part2 | 029C_lot | 3/1/2022 | 98.54 | Cold | FT | Fi_Insertion |
die5 | part2 | 029C_lot | 2/26/2022 | 98.35 | Hot | FT | Fi_Insertion |
die5 | part2 | 30CA_lot | 1/29/2022 | 95.85 | Hot | FT | CA_Insertion |
die5 | part4 | 031A_lot | 2/10/2022 | 92.14 | Hot | FT | Fi_Insertion |
die5 | part4 | 031A_lot | 2/15/2022 | 93.05 | Cold | FT | Fi_Insertion |
die5 | part4 | 031A_lot | 2/10/2022 | 96.47 | Hot | FT | Fi_Insertion |
die5 | part4 | 031A_lot | 2/15/2022 | 95.67 | Cold | FT | Fi_Insertion |
die5 | part4 | 031A_lot | 2/15/2022 | 92.46 | Cold | FT | CA_Insertion |
die5 | part4 | 031A_lot | 2/10/2022 | 90.17 | Hot | FT | CA_Insertion |
die5 | part4 | 031B_lot | 2/7/2022 | 93.83 | Hot | FT | Fi_Insertion |
die5 | part4 | 031B_lot | 2/9/2022 | 91.63 | Cold | FT | Fi_Insertion |
die5 | part4 | 031B_lot | 2/7/2022 | 96.12 | Hot | FT | Fi_Insertion |
die5 | part2 | 035C_lot | 2/26/2022 | 94.64 | Cold | FT | CA_Insertion |
die5 | part2 | 35CA_lot | 3/1/2022 | 94.1 | Cold | FT | Fi_Insertion |
die5 | part2 | 35CA_lot | 3/1/2022 | 95.87 | Cold | FT | Fi_Insertion |
die5 | part2 | 35CA_lot | 2/28/2022 | 93.72 | Cold | FT | CA_Insertion |
See if this works for you:
% Yield Val =
AVERAGE('Table'[% Yield])
Overall Yield =
AVERAGEX (
SUMMARIZE (
'Table',
'Dim Die'[DIE_TYPE],
'Dim Flow'[D flow],
'Dim Ft Product'[FT_PRODUCT],
'Dim Insertion'[D Insertion],
'Dim Lot'[LOTNO],
'Table'[Insertion Temp]
),
[% Yield Val]
)
Yield By Lot =
VAR Lot =
MAX ( 'Dim Lot'[LOTNO] )
RETURN
IF (
ISBLANK ( [% Yield Val] ),
BLANK (),
AVERAGEX (
FILTER ( ALL ( 'Table' ), RELATED ( 'Dim Lot'[LOTNO] ) = LOT ),
[% Yield Val]
)
)
Yield Change = [Overall Yield] - [Yield By Lot]
I've attached the sample BPBIX file
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
I was out for a few days, i tried your code yesterday, and it works.
Thank you very much
Regadrs,
Karim
It would be more productive if you provided sample data and a depiction of the expected outcome. Otherwise we are basically guessing!
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |