Forum Discussion
Measure to sum values from 2 different columns considering different status
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,
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.
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" ) )
5 Replies
- AlB
Community Champion
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.
- AlexisOlson
Super User
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" ) )