Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
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
CS
Solved! Go to Solution.
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
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
Hi Xiao Xin/ @v-shex-msft
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
CS
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
Hi XiaoXin / v-shex-msft
Yes indeed i feel doesnt really utilize/suit using Power BI with the GL thingy as well...not so easy to read and maintained, especially when i have more hierarchy and metrics.
Really appreciate for the help and time and effort for the solution.
Regards
CS
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |