topic Re: Measure to sum values from 2 different columns considering different status in DAX Commands and Tips

Measure to sum values from 2 different columns considering different status

Anonymous — Fri, 19 Feb 2021 20:10:03 GMT

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,

Re: Measure to sum values from 2 different columns considering different status

AlB — Fri, 19 Feb 2021 21:15:59 GMT

Hi Anonymous

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.

Re: Measure to sum values from 2 different columns considering different status

AlexisOlson — Fri, 19 Feb 2021 22:06:30 GMT

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" ) )

Re: Measure to sum values from 2 different columns considering different status

Anonymous — Sat, 20 Feb 2021 01:00:46 GMT

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 !!!

Re: Measure to sum values from 2 different columns considering different status

Anonymous — Sat, 20 Feb 2021 01:01:22 GMT

Thank you so much,

Your suggestion worked very well !!!

Re: Measure to sum values from 2 different columns considering different status

AlB — Sat, 20 Feb 2021 21:31:04 GMT

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.