Calculate % Changes based on group

HI satubuku83,

Actually, your requirement not very suitable for power bi. I think it should be more simply to achieve in excel pivot table.

I also shared power bi version below:

1. Grouping and transform table.

2. Write measure formulas:

Condition Total = 
VAR categoryLevel =
    CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] <> "OutWard" )
        - CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] = "OutWard" )
VAR premium =
    CALCULATE (
        CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] <> "OutWard" )
            - CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] = "OutWard" ),
        GL[Categroy] = "Premium"
    )
VAR claim =
    CALCULATE (
        CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] <> "OutWard" )
            - CALCULATE ( SUM ( GL[Amount] ), GL[SubCategory] = "OutWard" ),
        GL[Categroy] = "Claim"
    )
RETURN
    IF (
        ISFILTERED ( GL[Categroy] ),
        IF (
            ISFILTERED ( GL[SubCategory] ),
            SUM ( GL[Amount] ),
            CALCULATE ( categoryLevel, VALUES ( GL[Categroy] ) )
        ),
        premium - claim
    )


Changes = 
IF (
    ISFILTERED ( GL[SubCategory] ),
    SWITCH (
        SELECTEDVALUE ( GL[Categroy] ),
        "Premium", IF (
            SELECTEDVALUE ( GL[SubCategory] ) <> "In Stock",
            CALCULATE (
                SUM ( GL[Amount] ),
                VALUES ( GL[Categroy] ),
                VALUES ( GL[SubCategory] )
            )
                / CALCULATE (
                    SUM ( GL[Amount] ),
                    VALUES ( GL[Categroy] ),
                    GL[SubCategory] = "In Stock"
                )
        ),
        "Claim", IF (
            SELECTEDVALUE ( GL[SubCategory] ) <> "In Stock",
            CALCULATE (
                SUM ( GL[Amount] ),
                VALUES ( GL[Categroy] ),
                VALUES ( GL[SubCategory] )
            )
                / CALCULATE (
                    SUM ( GL[Amount] ),
                    GL[Categroy] = "Premium",
                    VALUES ( GL[SubCategory] )
                )
        )
    ),
    IF (
        SELECTEDVALUE ( GL[Categroy] ) = "Claim",
        DIVIDE (
            CALCULATE ( [Condition Total], GL[Categroy] = "Claim" ),
            CALCULATE ( [Condition Total], GL[Categroy] = "Premium" ),
            -1
        )
    )
)

3. Create matrix visual.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKzc0szVXSUTI0MICQQDpWB1nGCCxjiilhDJYwQRVXCErNzCsuLUrMS05V8MwrTyxKgSrCNANFrX9pCVSxKVixGUyxc05iZq6CS2ZRanIJRBzqXJySRiiSWB1kDlZojFMhwjUWCF/GAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Metric = _t, #"GL Code" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Metric", type text}, {"GL Code", Int64.Type}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Categroy", each Text.Split([Metric]," "){0}),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Categroy"}, {{"Content", each Table.AddColumn(_, "SubCategory", each Text.AfterDelimiter([Metric],"Reinsurance ")), type table}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"GL Code", "Amount", "SubCategory"}, {"GL Code", "Amount", "SubCategory"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Content",{"Categroy", "SubCategory", "GL Code", "Amount"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","","In Stock",Replacer.ReplaceValue,{"SubCategory"})
in
    #"Replaced Value"

Regards,

Xiaoxin Sheng