Forum Discussion

Frequent Visitor

8 years ago

Solved

Calculate % Changes based on group

Assuming I have this table.

Metric	GL Code	Amount
Premium	100	10000
Premium	200	5000
Premium	300	400
Reinsurance	400	5000
Reinsurance	500	5000

And I would like to build a new measure that keeps track of the % change (different formulae depending on the metric) as below. There will be cases new metric is added and % change(for checking) for that metric will be anoter division over group.

For the case below, no checking is needed for premium, but a % checking for reinsurance (total reinsurance/total premium)

	Total Amount	% Change (for checking)
Premium	15400	-
Reinsurance	10000	65%

Would appreciate if there is some light shed in this? Not too sure how filters would be able to help in this.

Regards

Tutorial Requests

Anonymous

8 years ago

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

GL Sample.pbix47 KB

4 Replies

Anonymous

Not applicable

8 years ago

Hi satubuku83,

Since power bi tables not contains column index and row index, I think you need to add a index column to achieve this requirement.

Steps:

1. Add group index.

2. Write measure.

% Changes = 
VAR index =
    MAX ( Metric[Index] )
VAR previous =
    LOOKUPVALUE ( Metric[Metric], [Index], index - 1 )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( Metric[Amount] ), VALUES ( Metric[Metric] ) ),
        CALCULATE ( SUM ( Metric[Amount] ), Metric[Metric] = previous ),
        BLANK ()
    )

3. Create matrix visual.

Regards,

Xiaoxin Sheng

satubuku83

Frequent Visitor

8 years ago

Hi Xiao Xin/ Anonymous

Always happy to hear from you.

Currently i am using Contains, will try out the method you suggested too!

% of Metric (Checking) = IF(CONTAINS('DIM_SOURCE',DIM_SOURCE[Metric],"Reinsurance"),DIVIDE([MTD Closing Balance],[MTD Gross Premium]),
if(CONTAINS('DIM_SOURCE',DIM_SOURCE[Metric],"Premium"),BLANK()))

Anyway, do you have any good way to do GL statement in Power BI? Its driving me crazy.

Assuming same data structure with additional data.

Metric	GL Code	Amount
Premium	100	10000
Premium	200	5000
Premium	300	400
Premium Reinsurance Inward	400	5000
Premium Reinsurance Outward	500	6000
Claim Direct	600	100
Claim Direct	600	200
Claim Reinsurance Inward	700	300
Claim Reinsurance Outward	800	400

Any clever way using DAX/ modeling to achieve the following? (ignore the remark - for your understanding purposes on the formulae)

Really appreciate for your time!

Remark for Amount	Metric	Amount	% Changes	Remark for % Changes
	Premium	15400
	Premium Reinsurance Inward	5000	32%	Premium Reinsurance Inward / Premium
	Premium Reinsurance Outward	6000	39%	Premium Reinsurance Outward / Premium
Premium + Premium Reinsurance Inward - Premium Reinsurance Outward	Gross Premium	14400

	Claim Direct	300	2%	Claim Direct/ Premium
	Claim Reinsurance Inward	300	6%	Claim Reinsurance Inward/ Premium Reinsurance Inward
	Claim Reinsurance Outward	400	7%	Claim Reinsurance Inward/ Premium Reinsurance Outward
Claim Direct + Claim Reinsurance Inward - Claim Reinsurance Outward	Gross Claim	200	1%	Gross Claim/ Gross Premium

Gross Premium - Gross Claim	Net Amount	14200

Regards

Anonymous

Not applicable

8 years ago

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

GL Sample.pbix47 KB

Forum Discussion

Calculate % Changes based on group

4 Replies

Related content

Calculate growth based on calculation group item

Calculate YoY change

Calculate Based on Filtered Segment

Dynamically Change Calculation Based on Slicer

Dynamic change Ranking based on Dimension change