Forum Discussion

paulfink's avatar
paulfink
Post Patron
6 years ago
Solved

Setting a Quarterly Target

hi guys,   Is it possible to set a quarterly target?   So the sales team have to hit a quarterly target that is a fixed number for this year. goes something like if salesperson >= quarterly targ...
Need Help
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi paulfink ,

     

    Assuming  you have a sale table named  'Table1' which have column [SalesPerson], [SaleAmount] and [Date], then you may create measure like DAX below .

     

    Award =
VAR _SaleTarget = 100
VAR _AwardAmount = 1000
VAR _TotalSale =
    CALCULATE (
        SUM ( Table1[SaleAmount] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[SalesPerson] = MAX ( Table1[SalesPerson] )
                && YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) )
                && QUARTER ( Table1[Date] ) = QUARTER ( MAX ( Table1[Date] ) )
        )
    )
RETURN
    IF ( _TotalSale > _SaleTarget, _AwardAmount, BLANK () )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

paulfink's avatar
paulfink
Post Patron
6 years ago

There is no sample data as i haven't been able to make anything that works.

 

Not sure what else to put.

 

This is going into a table that has other payment factors that go along with the sales team's plan.

 

It looks at the total sales they have made in the Quarter and will look at their target if it greater than or equal to it will award a fixed amount to them

v-xicai's avatar
v-xicai
Community Support
5 years ago

Hi paulfink ,

 

Assuming  you have a sale table named  'Table1' which have column [SalesPerson], [SaleAmount] and [Date], then you may create measure like DAX below .

 

Award =
VAR _SaleTarget = 100
VAR _AwardAmount = 1000
VAR _TotalSale =
    CALCULATE (
        SUM ( Table1[SaleAmount] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[SalesPerson] = MAX ( Table1[SalesPerson] )
                && YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) )
                && QUARTER ( Table1[Date] ) = QUARTER ( MAX ( Table1[Date] ) )
        )
    )
RETURN
    IF ( _TotalSale > _SaleTarget, _AwardAmount, BLANK () )

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.