Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |