Pivot table Groups

Copper Contributor

Hello,

 

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

@Quentin1625 

For the such sample

image.png

if we add data to data model and create calculated column

image.png

as

=
VAR currentScenario = Source[Scenario]
VAR totalScenario =
    CALCULATE (
        SUM ( Source[Quantity] ),
        ALLEXCEPT ( Source, Source[Scenario] ),
        Source[Scenario] = currentScenario
    )
VAR totalExchange =
    CALCULATE (
        SUM ( Source[Quantity] ),
        ALLEXCEPT ( Source, Source[Exchange] ),
        Source[Scenario] = currentScenario
    )
VAR exchangePercent =
    DIVIDE ( totalExchange, totalScenario, BLANK () )
RETURN
    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.