Count If Measure1 > Measure2

[moizsherwani]

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

[moizsherwani]

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

[Phil_Seamark]

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]

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]

Hi @moizsherwani,

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

[moizsherwani]

@Phil_Seamark also on the cumulative measure I am summing a column whereas here I am trying to sum a measure :(. any ideas?

[Phil_Seamark]

Hi @moizsherwani,

Sorry for the delayed response.  It's hard for me to tell where you have used pre-calculated data and where you are using measures and this makes a difference in the measure I would suggest.

Any chance you can strip out your PBIX file down to the bare minimum and share it with me so I can have a look.

Feel free to send the link via private message, but I will repond with any reply here 🙂

[moizsherwani]

@Phil_Seamark, sorry for the delayed response from my side this time. I may not be able to share the file at any level due to security reasons but the way I resolved this was using a supportive calculated column.

In my Target table I created a calculated column that would cumulatively sum the sales for each month from the SALES table and display them next to each month target

MonthSales = CALCULATE(

                                          SUM(SALES[Amount]),
                                                  FILTER(

                                                          SALES,
                                                              SALES[OrderDate]<='TARGET'[TargetEndDate]&&
                                                             YEAR(SALES[OrderDate])=YEAR('TARGET'[TargetEndDate])
                                                              )
)

I then added a measure to my target table

Points = CALCULATE(
                          COUNT('TARGET'[TARGETId]),
                                                 FILTER('TARGET',
                                                'TARGET'[MonthSales]>'TARGET'[TargetAmount]
                                                            )
                                     ) * 2

This does the work for me, if you notice I multiplied the count by 2 which gives me the points, so the logic was that count all the target rows where the sales > goal. So the reason I shared this with you was to also get your thoughts on the solution and a better one if it exists, I have heard it is not good practice to add calculated columns so I try my best not to do so but this time I could not figure out another way.

[Phil_Seamark]

Hi @moizsherwani,

Well done and it looks good.  Calculated columns have their place and can be very useful, particularly if you want to lock a value to the row.  You'll just have to decide what works best for you in this case.

Its easy enough to convert across to a Measure if you need,. 

[moizsherwani]

I am using something along the lines of 

GoalTarget = CALCULATE(
                           SUM('Goal'[TargetMoney]),
                              FILTER('Goal',
                                  'Goal'[GoalStartDate]<LASTDATE(CommonDate[Date])+1&&
                                    year('Goal'[GoalStartDate])=YEAR(LASTDATE(CommonDate[Date])))
                                    )

So I tried this as well but I am not sure what to replace the SUM('Goal'[TargetMoney]) with? Something along the lines of For Each month where SalesAccumulative>TargetAccumulative,  x= x +2.

[moizsherwani]

I use something like 

GOALACCUMULATIVE = CALCULATE(
                                           SUM(GOALAMOUNT),
                                                            FILTER(GOAL
                                                             GOALSTARTDATE<LASTDATE(CommonDate[Date])&&
                                                             year(GOALSTARTDATE)=YEAR(LASTDATE(CommonDate[Date])))
)

I tried something similar but I don't know what to do with the SUM(GOALAMOUNT) in the above formula <-- how do I tell it to keep adding a 2 for every SALES>GOAL