Forum Discussion

moizsherwani's avatar
moizsherwani
Continued Contributor
9 years ago

Count If Measure1 > Measure2

Hi guys,

 

I have two measures related to two distinct tables, so SALESACCUMULATIVE (running total of sales) to SALES and TARGETACCUMULATIVE (running total of target) to TARGET. I want give two points to the salesman if SALESACCUMULATIVE > TARGETACCUMULATIVE by month so this is what the table would look like

 

DATE - SALESACCUMULATIVE - TARGETACCUMULATIVE - POINTS

JAN         100                                    100                                2

FEB          150                                    200                               0

MAR         350                                   300                               2

TOTAL                                                                                     4

Need Help

9 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi moizsherwani,

     

    So are you after the calcuation for your Points column?

     

    If so it could be this :

     

    points = if([SALESACCUMULATIVE] > [TARGETACCUMULATIVE] , 2 , 0 )
    • moizsherwani's avatar
      moizsherwani
      Continued Contributor
      9 years ago

      It does not work when I do that because these are running totals. What happens is that by the end it is comparing the total running target with the total sales whereas it should be adding the sum of each month

      MONTH TARGET SALE POINTS

      JAN             2         3        2                

      FEB             4          4        0

      MAR           6          7        2

       

      ENDS UP AS

      TOTAL         6          7        2

       

      SHOULD BE                      4

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi moizsherwani,

         

        What is the formula you are using for the cumulative values?  You can probably use something like this for your POINTS column