Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 co...
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much,

      Your suggestion worked very well !!!

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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