Forum Discussion
selpaqm
Helper V
Helper VUse EARLIER with if
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.
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 )
2 Replies
- mahoneypat
Microsoft Employee
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
