# Power BI Exchange

View Only

## sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

• #### 1.  sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Posted May 27, 2022 02:52 AM
sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Here in the sales over annual quota , Negative amount must return as zero for Trauma and same positive amount is already correct showing up under Extremities.
I Tried IF Then Else logic (0+\$4,22,47,253 =  0) didn't worked , will return sum of these two numbers as zero.
As said (\$2,81,07,54,662) as 0+\$4,22,47,253 =  \$4,22,47,253 (Desired Output)
Need help on other way of work around to build using Dax measure.

------------------------------
sridhama Kundur
------------------------------

• #### 2.  RE: sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Posted May 28, 2022 05:12 AM

Hi Sridhama,

strange numbers you have  :)

What about SWITCH(TRUE(),[Variance to Quota]<0,0,[Variance to Quota])

Regards

------------------------------
------------------------------

• #### 3.  RE: sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Posted May 29, 2022 05:15 AM

when I use switch dax function, am seeing total as zero..
Total must be \$4,22,47.253 right

Need an dax measure to resolve this.

Thank You

------------------------------
sridhama Kundur
------------------------------

• #### 4.  RE: sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Posted May 31, 2022 06:36 AM
HI Sridhama,
I think you should create dax measure for total sales-quota with calculate or sumx function then create measure (sales-quota-adjusted) again it to adjust 0 value for negative value.
And than create matrix with trauma and extrimities as category and geo as sub-categories and (sales-quota-adjusted) for value. Than you can get total, grand total by fly

------------------------------
Dili Timor
12510
------------------------------

• #### 5.  RE: sales over annual quota(Sales-Quota) Total value if Negative must return zero and if positive return same positive value

Silver Contributor
Posted 29 days ago
Hi @sridhama Kundur. Have you tried something like this?

Variance to Quota = IF(    [Sales]-[Quota] <= 0,   0,    [Sales]-[Quota] )

------------------------------
Nancy Peterson