How to create an OTHERS category

[PerryStrine]

Hey all,
I have some sales data in a database.
I import the data into PP and sum the sales by product id. The result looks like this:
ProdID TotalQtySold
P2 120
P5 86
P1 70
P9 32
P3 8
P4 6
P6 3
P7 3
P8 1

I would like to add any totals that are less than 10 to a category called Others.
Then my pivot would like this this
ProdID TotalQtySold
P2 120
P5 86
P1 70
P9 32
Others 21

Is there any way to achieve this or do I need to modify the source data is some way?
Perry

 

[ArthriticPanda]

Hi PerryStrine,

It sound like a calculated field may cover this. Here is a link to creating calculated fields in pivot tables:

Calculate values in a PivotTable report - Excel - Office.com

Hope this helps,

AP

 

[powerpivotpro]

Power Pivot doesn't let you put anything on rows/columns/filters/slicers unless that value explicitly exists in the PP tables.

In other words, "Other" would need to exist in your Products table, and be explicitly handled in your measure:

IF(HASONEVALUE(Products[ProdID]), IF(FIRSTNONBLANK(Products[ProdID])="Other", sum products LESS than 10, sum products GREATER than 10), sum products GREATER than 10)

and the greater than 10 part looks like:
CALCULATE(SUM(Sales[Amt]), FILTER(Products, SUM(Sales[Amt]) >= 10))

not exactly elegant.