Switch to negative based on Multiple conditions (complex)

[Anonymous]

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

[VahidDM]

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 · Accepted solution]

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]

Hi @Anonymous ,

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

Best Regards,
Winniz