Pivot table Groups

Copper Contributor



I have imported results from the life cycle assessment tool UMBERTO into excel. In the pivot table you can see the indicator results for 3 different scenarios of paper production based on all In and Outputs (Exchanges). Since there are so many exchanges with minor relevance, I would like to only specify exchanges which contribute more than 5 % to the specific Indicator. All exchanges which contribute less should be summed up in a group called "others" within each of the 3 scenarios. 

excel issue.jpg

When tried grouping data under 5 % it would either show everything under or everything below but not both at the same time.


How can I achieve this?


Thanks for any help

1 Reply


For the such sample


if we add data to data model and create calculated column



VAR currentScenario = Source[Scenario]
VAR totalScenario =
        SUM ( Source[Quantity] ),
        ALLEXCEPT ( Source, Source[Scenario] ),
        Source[Scenario] = currentScenario
VAR totalExchange =
        SUM ( Source[Quantity] ),
        ALLEXCEPT ( Source, Source[Exchange] ),
        Source[Scenario] = currentScenario
VAR exchangePercent =
    DIVIDE ( totalExchange, totalScenario, BLANK () )
    IF ( exchangePercent > 0.05, Source[Exchange], "other" )

plus measure

Qty:=SUM( Source[Quantity] )

we may use it to create PivotTable as above.

Not optimized from performance point of view, just an idea.