Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Switch to negative based on Multiple conditions (complex)

My data has a  calculated field for  Return of Investment = (MTD op income/Days in month)*365/MTD Net Investment)

My DAX = CALCULATE ( MAX (CoName ) = "RETURN ON INVESTMENT",
            DIVIDE (
                DIVIDE (
                    CALCULATE (
                        SUM ( Tbname'[ThismonthAmount] ),
                        FILTER (
                            ALLSELECTED ( Tname),
                            'Cname = "OPERATING INCOME"
                        )
                    ),
                    dayscount,
                    0
                ) * 365,
                CALCULATE (
                    SUM ( Tbname'[ThismonthAmount]  ),
                    FILTER (
                        ALLSELECTED ( Tbname),
                        'colname = "NET INVESTMENT"
                    )
                ),
                0

 

My issue is the Net Investment at times is negative and so is operating income.  I need to add condional stament to check (

Both operating income and net investment are negative. ROI % should be negative.

 when negative Net Investment & Positive Operating Income ROI % should show as 9999.99). I have created variable for both operating income and net investment . Can someone please help me with the DAX  that first check the above condition and based on that applies a negative sign or inserts 9999.99 

 

TIA

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks VahidDM 

    I  tried  both options and the issue is at times i get 9999.00  and the  first condition gives 0 or vice versa am I incorrectly applying it TIA

     

    CALCULATE ( MAX ( 'consolidate AggSOPAccountLevel'[SOPLineDesc] )
    ) = "RETURN ON INVESTMENT", SWITCH(TRUE(), [Operating INCOME] < 0
    && [Net Investment] < 0, ROI*-1, [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99",0),
    //IF (
    // [Operating INCOME] < 0
    // && [Net Investment] < 0,
    //ROI *-1,
    // IF ( [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99", ABS ( roi ) )
    // ),

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous 

     

    You need to create measue like this by using those variables:

     

    Final ROI % = if([Net Investment]<0&&[Operating Income]<0,ABS(ROI %)*-1,if([Net Investment]<0&&[Operating Income]>0, 9999.99,ROI %))

     

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks VahidDM 

      I  tried  both options and the issue is at times i get 9999.00  and the  first condition gives 0 or vice versa am I incorrectly applying it TIA

       

      CALCULATE ( MAX ( 'consolidate AggSOPAccountLevel'[SOPLineDesc] )
      ) = "RETURN ON INVESTMENT", SWITCH(TRUE(), [Operating INCOME] < 0
      && [Net Investment] < 0, ROI*-1, [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99",0),
      //IF (
      // [Operating INCOME] < 0
      // && [Net Investment] < 0,
      //ROI *-1,
      // IF ( [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99", ABS ( roi ) )
      // ),
      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Could you please share sample data or PBIX file without sensitive data?

         

        Best Regards,
        Winniz