Pivot table Totals is empty due to set expression?

[user467341]

I have the following pivot table

The totals for first column is empty. After doing some troubleshooting, I figured it was due to my set expression set inside my measure:

=IF([Stage]<>'Removed',SUM(Hours))

When I change this set expression to

=SUM(Hours)

The total will work:

You can see that "Break" has double entry, that is because I did some data processing to aggregate any parts with "removed" into the break stage (hence my first picture). However, I can't use this set expression as this will report double hours for "Break".

I figured it must be due to the ELSE of my set expression, which is NULL.

How can I get my output to be this:

[MayilVahanan]

Hi

Try like below

=SUM({<[Stage]-={'Removed'}>}Hours)

[user467341]

Hi Mayil, thanks for the reply. This will remove the column "-", which thus remove rows from body and gears (6.3 and 32 respectively) which is not I want from my expected output.

[MayilVahanan]

Can you send the sample data in excel and let us know the expected output.

[user467341]

Hi Mayil, the expected output is in my original post, third picture.