Conditional sum

[javedbh]

Need to calculate sum of BinCount based on a condition:

I have the following data in raw form.

Category	Device	Lot	Screen	BinName	BinFlag	BinCount
Cat1	Dev1	L1	0	1	P	500
Cat1	Dev1	L1	0	2	F	300
Cat1	Dev1	L1	0	3	F	200
Cat1	Dev1	L1	1	1	P	200
Cat1	Dev1	L1	1	2	F	150
Cat1	Dev1	L1	1	3	F	100
Cat1	Dev1	L1	1	4	F	50
Cat1	Dev1	L1	2	1	P	200
Cat1	Dev1	L1	2	5	F	100
Cat1	Dev1	L2	0	1	P	600
Cat1	Dev1	L2	0	3	F	100
Cat1	Dev1	L2	0	4	F	100
Cat1	Dev1	L2	0	9	F	50
Cat1	Dev1	L2	1	1	P	150
Cat1	Dev1	L2	1	3	F	60
Cat1	Dev1	L2	1	4	F	40

It can also be represented as following:

					BinName
Lot	Screen	Total	P	F	1	2	3	4	5	9
L1	0	1000	500	500	500	300	200
L1	1	500	200	300	200	150	100	50
L1	2	300	200	100	200				100
L2	0	850	600	250	600		100	100		50
L2	1	250	150	100	150		60	40

The formula to calculate sum of BinCount:

If BinFlag = P then take sum of BinCount from all Screen(s) of that Lot

If BinFlag = F then take sum of BinCount from last Screen of that Lot

e.g.

For Lot = L1

If BinFlag = P then take sum of BinCount from all Screen(s) of Lot=L1

If BinFlag = F then take sum of BinCount from Screen=2 of Lot=L1

For Lot = L2

If BinFlag = P then take sum of BinCount from all Screen(s) of Lot=L2

If BinFlag = F then take sum of BinCount from Screen=1 of Lot=L2

When data is grouped by Lot and BinName:

Category	Device	Lot	BinName	BinFlag	BinCount
Cat1	Dev1	L1	1	P	900
Cat1	Dev1	L1	2	F	0
Cat1	Dev1	L1	3	F	0
Cat1	Dev1	L1	4	F	0
Cat1	Dev1	L1	5	F	100
				Total	1000
Cat1	Dev1	L2	1	P	750
Cat1	Dev1	L2	3	F	60
Cat1	Dev1	L2	4	F	40
Cat1	Dev1	L2	9	F	0
				Total	850

It can also be represented as following:

				BinName
Lot	Total	P	F	1	2	3	4	5	9
L1	1000	900	100	900				100
L2	850	750	100	750		60	40

And when data is grouped by Device and BinName:

Category	Device	BinName	BinFlag	BinCount
Cat1	Dev1	1	P	1650
Cat1	Dev1	2	F	0
Cat1	Dev1	3	F	60
Cat1	Dev1	4	F	40
Cat1	Dev1	5	F	100
Cat1	Dev1	9	F	0
			Total	1850

See that the data is first grouped by lot and then summed for device.

Any help is appreciated. Thanks.

[javedbh]

Anybody plz....

[KHorseman]

If the output you want is that last table, this looks like just a regular sum of BinCount. If you place those other columns into a matrix visual and add a measure for the sum, the row context will take care of the conditions for you.

Sum of BinCount = SUM(Tablename[BinCount])

[javedbh]

@KHorseman It is not what I require.

Here is the formula to calculate sum of BinCount:

If BinFlag = P then take sum of BinCount from all Screen(s) of that Lot

If BinFlag = F then take sum of BinCount from last Screen of that Lot

Correct results are highlighted with green, blue, and brown boxes. I want to know how to combine these three measure into a single measure.

[v-sihou-msft]

Hi @javedbh

If you want to calcualte the BinCount based on BinFlag and Screen group, you can use ALLEXCEPT() as filter in CALCULATE().

=CALCULATE(SUM(Sheet[BinCount]),ALLEXCEPT(Shee2,Sheet[BinFlag],Sheet[Screen]))

Reference:

ALLEXCEPT Function (DAX)

Regards,