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

     

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.