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 ...
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