Thanks Michael for the solution. I like the way how you make the calculated field and it's working for me. I would request you to please explain me this analog so that I can use it in future.
2 of 2 people found this helpful
The first part: FIRST()!=LAST() THEN SUM([Sale])
FIRST()!=LAST(). This means there are more than one row, which is this:
In this case, I only list the SUM(Sales) for each Product ID.
Second line: ELSE SUM(IF [Product]!='Y' THEN [Sale] END) END
Otherwise, it means only one row, which means grand total. Here, I simply exclude the sales for Product Y. So Grand total only add the sales of X and Z.
Is it clear?