Forum Discussion

gguadalupe's avatar
gguadalupe
Frequent Visitor
9 years ago
Solved

Insurance Earned Premium/Loss Ratio Calculation

Hi All! I'm new to PowerBi and I'm trying to implement an insurance dashboard. All is fine, but now I'm trying to implement the loss ratio formula controled by a slicer filled with my Dates table. ...
  • gguadalupe's avatar
    gguadalupe
    9 years ago

    v-jiascu-msft

     

    Hi Dale,

     

    I have been thinking about this, and I finally understood that my data model was wrong.

     

    Now the model has the info "flat" month by month, and I let Power Bi do just aggregations.

     

    With this model now I can see the Earned Premium month by month, and calculate the Loss Ratio change month by month.

     

    Thank you Dale for your help!!

     

    Gus.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

gguadalupe

 

Hi,

 

Is the PremiumID of the records (rows) in PremiumTable unique? If yes, here could be the solution.

 

It’s better to take Premium Per Day as a calculated column. The formula is: ( pay attention to the +1 in blue. )

 

Premium Per Day =
'PremiumTable'[PremiumAmt]
    / (
        DATEDIFF ( 'PremiumTable'[EffectiveDate], 'PremiumTable'[ExpirationDate], DAY )
            + 1
    )

Then we are going to create two measures.

Earned Premium =
MIN ( PremiumTable[Premium Per Day] )
    * CALCULATE (
        COUNTROWS ( DateTable ),
        FILTER (
            DateTable,
            DateTable[Date] >= MIN ( PremiumTable[EffectiveDate] )
                && DateTable[Date] <= MIN ( PremiumTable[ExpirationDate] )
        )
) 

 

Accumulated Earned Premium =
MIN ( 'PremiumTable'[Premium Per Day] )
    * CALCULATE (
        COUNTROWS ( DateTable ),
        FILTER (
            ALL ( DateTable ),
            'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
                && 'DateTable'[Date] >= MIN ( 'PremiumTable'[EffectiveDate] )
                && DateTable[Date] <= MIN ( PremiumTable[ExpirationDate] )
        )
    )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

MWinter225's avatar
MWinter225
Advocate IV
7 years ago

v-jiascu-msft  that was great and really helped me a lot but then how do I SUM those values to show a total amount at the bottom? 

thanks,

Matt

 

UPDATE:

Got it to sum up for a grand total by wrapping my measure in a SUMX.  SUMX(table, my previous measure)!

  • gguadalupe's avatar
    gguadalupe
    Frequent Visitor
    7 years ago

    Hi Matt,

     

    You mean the total amount in each column?

    I let the Matrix Control to do that.

    My model is as simple as posible so I try to use as much of the "out of the box" funcionalities.

     

     

    Gus.

    • MWinter225's avatar
      MWinter225
      Advocate IV
      7 years ago

      gguadalupe  Thanks for your response Gus. Sorry for the confusion, I tagged the microsoft associate v-jiascu-msft  in reference to his solution, which I was able to figure out and I updated my previous comment! thanks anyway!

      Matt