Pivot or Power Pivot: how to calculate the other as a remainder between subtotal and top N items

Question

I am trying to create a calculated item in Excel pivot that would calculate the "other" as the difference between subtotal of the group and top N selected items.

Here is the example of original data table and the current pivot table. The Product field is filtered by the top 2 items from Sales:

Is there any way to add a calculated item for each Group within the Country as the difference between the total Sales of the Product (before filtering) and the sum of the visible top N items of the Product?

The desired output would be like this:

Either Power Pivot or simple Pivot would work.

I would highly appreciate any ideas.

Can you mock up what the result should look like for this example? — Alexis Olson
@AlexisOlson - Sure - I inserted a pic with the desired output into the field with the original question. In other words subtotals by country and group are as if there would be no filtering, and the new item "_Other" is the difference between the Group subtotal and the sum of sales by visible (in this example top 2) items. I am thinking of VBA code for this but may be there is any way to create such table with Power Piviot tools. Thanks! — Yury G
Great. The question is clearer now. Here's a related one for comparison: stackoverflow.com/questions/38087699/… — Alexis Olson
@AlexisOlson - Thank you so much for such a detailed answer!! So sorry to ask you a basic questions, but I am just doing my first steps in PowerPivot and PowerQuery - where to put the code (measure) that you provided? Or if there is any guide to it I will be grateful for the reference... Thanks again! — Yury G
Either in the Calculation Area of the Power Pivot window or from the Measures button on the Power Pivot ribbon tab — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-05T18:37:30

I'll answer this along the same lines as the linked post, but with some modifications.

First, we need to add another product to serve as the remainder outside of the top 2. I'm going to name this "Remainder" since you already have "Other". Unlike the linked post, I'm going to create this as a separate dimension table, so my source data for the pivot table are two tables Table1 and Products that look like this:

You'll need to create a relationship between these tables on the Product column. Excel might be able to auto-detect but do it manually if you need to.

Like in the linked post, we'll define a couple of base measures to make the one we're after easier to write:

SumSales = SUM ( Table1[Sales] )

and

Product Rank = RANKX ( ALLSELECTED ( Products[Product] ), [SumSales] )

With these defined, we can write the measure that we actually want to use:

Top 2 and Remainder =
IF (
    HASONEVALUE ( Products[Product] ),
    IF (
        [Product Rank] <= 2,
        [SumSales],
        IF (
            VALUES ( Products[Product] ) = "Remainder",
            SUMX (
                FILTER ( ALLSELECTED ( Products[Product] ), [Product Rank] > 2 ),
                [SumSales]
            )
        )
    ),
    [SumSales]
)

Using the Table1[Country], Table1[Group], and Products[Product] for the rows in the pivot table and this measure we get:

Note that since I'm using ALLSELECTED instead of ALL for the ranking, this measure should still work when you apply filters to your data.

Pivot or Power Pivot: how to calculate the other as a remainder between subtotal and top N items

1 Answers