Measure to sum values from 2 different columns considering different status

[dalmofm]

Hello Guys,

I need to create a measure to sum values from 2 different columns considering the specific status.

What I need is: if the "Status Column" is filled with 'Indemnified' I need to consider in the sum the values from the column "Payed Value", but if the "Status Column" is filled with 'Pending', I need to consider in the sum the values from the column "Estimated Loss".

For example:

Date of the claim	Cause	Estimated Loss	Payed Value	Status
2020-03-22	Electrical Damage	$              500,00	$         375,00	Indemnified
2020-03-25	Robery	$          1.500,00	$         750,00	Indemnified
2020-05-04	Fire	$              250,00	$         225,00	Indemnified
2020-05-10	Fire	$          7.500,00	$     7.500,00	Indemnified
2020-10-21	Fire	$              345,00	$         345,00	Indemnified
2020-11-13	Robery	$          4.330,00	0	Pending
2020-12-25	Explosion	$          1.200,00	0	Pending
2020-12-25	Electrical Damage	$              450,00	$         337,50	Indemnified
2021-01-01	Electrical Damage	$          3.400,00	0	Pending

Thank you,

[AlB · Accepted solution]

Hi @dalmofm 

You need to be more specific. What is the expected result for the data  above?

Try

Measure =
SUMX (
    Table1,
    IF (
        Table1[Status] = "Indemnified",
        Table1[PaidValue],
        IF ( Table1[Status] = "Pending", Table1[EstimatedLoss] )
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @dalmofm 

You need to be more specific. What is the expected result for the data  above?

Try

Measure =
SUMX (
    Table1,
    IF (
        Table1[Status] = "Indemnified",
        Table1[PaidValue],
        IF ( Table1[Status] = "Pending", Table1[EstimatedLoss] )
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[dalmofm]

The result of this sum it will represent my "loss ratio" with the insurer, what means that is possible I will face an increase in the renewal cost of this insurance.

Thank you so much,

Your suggestion worked very well !!!

[AlexisOlson · Accepted solution]

In general, it's better to use filters than IFs inside of an iterator. So I'd suggest an alternative:

NewMeasure =
CALCULATE (
    SUM ( Table1[Payed Value] ),
    FILTER ( Table1, Table1[Status] = "Indemnified" )
) +
CALCULATE (
   SUM ( Table1[Estimated Loss] ),
   FILTER ( Table1, Table1[Status] = "Pending" )
)

[AlB]

@AlexisOlson 

True.  IFs within an iterator are usually a drag on performance.

It is also better to filter only on columns rather than on the full table:

NewMeasure V2 =
CALCULATE ( SUM ( Table1[Payed Value] ), Table1[Status] = "Indemnified" )
    + CALCULATE ( SUM ( Table1[Estimated Loss] ), Table1[Status] = "Pending" )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[dalmofm]

Thank you so much,

Your suggestion worked very well !!!