Forum Discussion

Quentin1625's avatar
Quentin1625
Copper Contributor
Aug 01, 2023

Pivot table Groups

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. 

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

BI & Data Analysis
Charting
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 08, 2023

    Quentin1625 

    For the such sample

    if we add data to data model and create calculated column

    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.

Resources