Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 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



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 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
    Hi Adam,

    Thank You for your suggestion

    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
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------