Use EARLIER with if

[selpaqm]

Hi,

i have a table like below.  

ID	Name1	Name2	Total	shipped	true/false	Ordertype
8	A	X	1000	300	TRUE	W
15	A	X		1200	FALSE	C
7	C	X		500	TRUE	X
16	K	X		300	TRUE	W
10	C	Y	300	100	TRUE	Q
11	D	Y		200	TRUE	W
17	F	Y		400	TRUE	Q

first i need to find weight average for name2 related shipped (as weight)  and total (value which is will be seperated for empty cells.

Total Cost based total = DIVIDE(CALCULATE(SUM(Sheet1[Total]),FILTER(Sheet1,Sheet1[Name2]=EARLIER(Sheet1[Name2])))*Sheet1[shipped],CALCULATE(SUM(Sheet1[shipped]),FILTER(Sheet1,Sheet1[Name2]=EARLIER(Sheet1[Name2]))))

this formula can fix my first part.

however, i need to add if to this formula because if there is an false in true/false column related for name2 i want to ignore the total and all total cost section for same name2 need to be zero.

for above table, Total Cost based total column against X should be zero.

hope everything is clear.

[mahoneypat · Accepted solution]

You could consider this approach:

Total Cost Based Total =
VAR adjcosttotal = (your current expression)
VAR Falserows =
CALCULATE (
COUNTROWS ( Sheet1 ),
ALL(Sheet1), Sheet1[Name]=EARLIER(Sheet1[Name]),
Sheet1[true/false] = FALSE ()
)
RETURN
IF ( Falserows > 0, 0, adjcosttotal )

[selpaqm]

works fine,

thanks @mahoneypat