topic Re: Creating a measure to show 0 if the two divided numbers are both negative. in DAX Commands and Tips

Creating a measure to show 0 if the two divided numbers are both negative.

kmschroer — Tue, 27 Sep 2022 18:45:39 GMT

Hi everyone - I have the following measure:

PROFIT % OF NSR = divide(sum('Office Earnings'[Group Task NumberCurrent Profit]),sum('Office Earnings'[Group Task NumberCurrent Net Revenue]))

Is there a way to add a line to this that makes the "Profit % of NSR" zero if the sum of 'Net Revenue' and sum of 'Profit' are both negative numbers?

Re: Creating a measure to show 0 if the two divided numbers are both negative.

tackytechtom — Tue, 27 Sep 2022 18:54:32 GMT

Hi kmschroer ,

How about :

IF ( 
    AND ( 
        SUM ( 'Office Earnings'[Group Task NumberCurrent Profit] ) < 0, 
        SUM ( 'Office Earnings'[Group Task NumberCurrent Net Revenue] ) < 0 
    ),
    0,
    DIVIDE (
        SUM ( 'Office Earnings'[Group Task NumberCurrent Profit] ),
        SUM ( 'Office Earnings'[Group Task NumberCurrent Net Revenue] )
    )
)

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Creating a measure to show 0 if the two divided numbers are both negative.

kmschroer — Wed, 28 Sep 2022 12:45:44 GMT

tackytechtom You are awesome! That worked perfectly thank you so much!