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 Everyone,
im working on power bi report where i need to multiply "Losses 3 Years Rate" measure values based on Index Year in matrix
below is the formula of "Losses 3 Years Rate":
Losses 3 Years Rate =
IFERROR(DIVIDE(
CALCULATE (
[Incurred Claims],
FILTER (
ALL( LineReport[Index Year],LineReport[UW Year]),
LineReport[Index Year] <= MAX ( LineReport[Index Year]) &&
LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
)
)
,
if( SELECTEDVALUE(LineReport[Index Year])=1,
CALCULATE(
[Incurred Claims],
FILTER (
ALL( LineReport[Index Year],LineReport[UW Year]),
LineReport[Index Year] <= MAX ( LineReport[Index Year]) &&
LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
)
),
CALCULATE (
[Incurred Claims],
FILTER (
ALL( LineReport[Index Year],LineReport[UW Year]),
LineReport[Index Year] <= MAX ( LineReport[Index Year])-1 &&
LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
)
)
),0)
it is properly generated as per the below screen shot:
now i need to calculate Developement Factor which is the "Losses 3 years Rate" for index year multiply by the others for example:
Dev Fac for index 1 = Rate1 * rate2*rate3*rate4*rate5*rate6*rate7*rate8 |
Dev Fac for index 2 = rate2*rate3*rate4*rate5*rate6*rate7*rate8 |
Dev Fac for index 3 = rate3*rate4*rate5*rate6*rate7*rate8 |
Dev Fac for index 4 = rate4*rate5*rate6*rate7*rate8 |
Dev Fac for index 5 = rate5*rate6*rate7*rate8 |
Dev Fac for index 6 = rate6*rate7*rate8 |
Dev Fac for index 7 = rate7*rate8 |
Dev Fac for index 8 = rate8 |
Im using the below formula:
Cape Cod IL 3 Years Rate =
var indexyear = SELECTEDVALUE(LineReport[Index Year])
return
EXP(
SUMX(
FILTER(ALL(LineReport), LineReport[Index Year]<=MAX(LineReport[Index Year]) &&
LineReport[Index Year]>= indexyear)
,
LN([Losses 3 Years Rate])
)
)
however the matrix is keep loading and throw a memrory error:
below is the formula i used in Matrix Value:
Rate Calculation =
VAR Val =
SWITCH(SELECTEDVALUE('Chain Ladder'[Group]),
"Premium 3 Years",[Premium 3 Years Rate],
"Premium 5 Years",[Premium 5 Years Rate],
"Premium 7 Years",[Premium 7 Years Rate],
"Premium All Years",[Premium All Years Rate],
"Premium Average",[Premium Average Years Rate],
"Losses 3 Years", [Losses 3 Years Rate],
"Losses 5 Years",[Losses 5 Years Rate],
"Losses 7 Years",[Losses 7 Years Rate],
"Losses All Years",[Losses All Years Rate],
"Losses Average",[Losses Average Years Rate],
"Development Factor",'Cape Cod'[Cape Cod IL 3 Years Rate]
)
RETURN
FORMAT(Val, "0.00000")
appreciate your assistance since i cant go any further with this.
Best regards,
Georges
Hi @gorgo
What about the code for [Incurred Claims]? Would it be possible for you to share the pbix?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
HI, incurred claims is a simple sum formula :
Incurred Claims = SUM(LineReport[Paid Claims])+[Outstanding Claims]
Outstanding Claims = if (SELECTEDVALUE('Year Type'[Type])="FY",
CALCULATE(SUM(LineReport[OS Claims]),LineReport[Last OS Position FY]=1),
CALCULATE(SUM(LineReport[OS Claims]),LineReport[Last OS Position UW Year]=1))
Last OS Position FY = IF(AND([Max Period From FY]= LineReport[Premium Period From],[Max Transaction From FY]=LineReport[Transaction Date]),1,0)
Last OS Position UW Year = IF(LineReport[Statement Type]="Claim Statement" ,
IF(LineReport[Transaction Date]=[Max Transaction Date],1,0),
IF(AND([Max Period From]=LineReport[Premium Period From],[Max Transaction Date]=
LineReport [Transaction Date]),1,0))
these are formulas involved.
note that i used Incurred Claims in other formulas and other tables, and it was generated normally
thanks
@gorgo - First, appreciate all of the information but this will be difficult to troubleshoot without recreating it and for that would need sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
With that said, you may be able to improve the performance of your DAX measures. For example, IFERROR is an expensive DAX function and you don't need it because you are already using DIVIDE which has built in error checking as an optional 3rd parameter. So I would get rid of that.
Other thoughts, you do not include your formula for Incurred Claims so there may be optimizations there. Also, do you really need ALL or can you use something like ALLEXCEPT or some other base filter, or maybe REMOVFILTERS perhaps? It is expensive to start with ALL as this is going to eat up a lot of memory when doing the DAX calculation.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |