DAX virtual summarized table

Regular Visitor

4 years ago

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

PaulDBrown

Community Champion

4 years ago

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

Yield.pbix60 KB

KarimMouloua
Regular Visitor
4 years ago
Hi Paul,

I was out for a few days, i tried your code yesterday, and it works.
Thank you very much

Regadrs,
Karim

Forum Discussion

Related content

Fabric virtual table

Summarize virtual table

Dynamic virtual summarized table

How to summarize virtual table table

Merge Summarize Table