Count SKUs where criteria is met (calculated field)

[esbencito]

Hi all,

I am struggling to use calculated fields instead of additional columns in the data set. I need to count the number of SKUs for different regions, but only if the order units (OO UNT) are greater than 0.

I have 5 columns where these conditions are already calculated directly in the data set (APAC, JPN, KOR, GCSEA, ANZ), so the pivot gets it from there.

Now I want a 6th pivotfield named "APAC (EXCL ANZ)", that is basically a calculated field which gets the number of SKUs for whole "APAC" without the region "ANZ".

Obviously I can create an additional column where this is calculated first, but since the data is pasted from somewhere else, I want to use a calculated field instead. How would that work? I tried, but don't seem to get the correct results...

I attached a sample file to make this more clear.

[rorya]

It won't work for the calculation you need unless you have Power Pivot and load the data to the data model. Your calculated field will always be calculating as the Max of the three totals, not as the total of the max for each detail row.

[esbencito]

making sense now... Thanks for clarifying! I assume I have to create additional columns then, as I currently cannot use the Power Pivot function.

[rorya]

Yes, you will need additional columns.