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

     

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

 

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.