Forum Discussion

Chanleakna123's avatar
Chanleakna123
Icon for Post Prodigy rankPost Prodigy
5 years ago

Total Measure Sum All even with IF conition

hi All , 

i have no idea what's wrong with this , 

i'm using below Measure : 

 

****%Selected = IFERROR([QTY Deliver]/'Selected Outlet'[Selected Target],0)

****CAN Selected = IF('Selected Outlet'[%Selected]> 0.979,CALCULATE([QTY Deliver],FILTER('Master Description Weekly','Master Description Weekly'[Weekly Stability ]="CAN")))*0.1
 
the result turn out 688 , while it should be less than that . 
the result of 688 is including with less than 0.979 which is wrong from my purpose .

 

Show and Tell

8 Replies

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

    Chanleakna123 ,

    Is this a measure selected Outlet'[Selected Target]?( if yes share calculations)

    Try like this and check the values

    %Selected = Divide([QTY Deliver], 'Selected Outlet'[Selected Target],0)

    • Chanleakna123's avatar
      Chanleakna123
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      hi , amitchandak  %Selected is not the problem , the problem is on CAN selected , 

      the result is included with less than 98% , which is not right. 

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Chanleakna123 Your total needs a row context to evaluate the IF condition. Based on the sample photo you have provided, I am going to guess you want Customer Code to provide the row level which will determine your SUM?

     

    See if this helps: 

    CAN Selected = SUMX(VALUES['Master Description Weekly'[Customer Code]), IF('Selected Outlet'[%Selected]> 0.979,CALCULATE([QTY Deliver],FILTER('Master Description Weekly','Master Description Weekly'[Weekly Stability ]="CAN")))*0.1)

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Chanleakna123  what is the formula for [QTY Deliver] please? There may be too many tables in use. Not sure if below change will help, so please share the DAX for the QTY Deliver measure so I have all the info. 

         

        CAN Selected = SUMX(VALUES['Master Data Weekly'[Customer Code]), IF('Selected Outlet'[%Selected]> 0.979,CALCULATE([QTY Deliver],'Master Description Weekly'[Weekly Stability ]="CAN"))*0.1)

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Chanleakna123  Part of the problem may be that 'Selected Outlet'[%Selected] has more than one value per Customer Code. In your table visual, what aggregation are you using on [%Selected]? My updated formula below assumes you're doing an AVERAGE, but you can change that to SUM or something else as needed. I have also changed the SUMX to a SUM on the Qty Delivered calculation and put it in the same measure for CAN Selected. Please let me know how this goes and what errors/results you get if any.

     

    CAN Selected = SUMX(VALUES['Master Data Weekly'[Customer Code]), IF(AVERAGE('Selected Outlet'[%Selected])> 0.979,CALCULATE(SUM('BOS Secondary'[QtyDelivered]),'Master Description Weekly'[Weekly Stability ]="CAN")*0.1))