Need assistance with DAX Measure

[JadeDoherty]

Hello - wondering if someone might be able to assist with a DAX Measure / Calculation.

Basically the sales team is looking to run a Sales promotion that will be based on a points system - where prizes will be given to the top 3 sales reps in the month.  The sales data contains a single row for each product order (as shown in the table below) and be points are based on the units only (price is not relevant).  The following rules apply to the promotion:

1. The promotion is only available to Salesreps from the Acquisition and Retention Teams
2. The promotion only applies to sales of Hammers and Bolts
3. 1 Point is awarded for each Hammer sale - with a maximum of 3 points awarded per customer per Salesrep
4. 2 Points are awared for each Bolt sale - with a maximum of 10 points per customer per Salesrep

The data is as follows:

Date	SalesRep	Customer	Product Category	Product	Price	Team
1/10/2020	Joe Bloggs	Acme Inc	Tools	Hammer	10.00	Acqusition
1/10/2020	Joe Bloggs	Acme Inc	Tools	Hammer	10.00	Acqusition
1/10/2020	Joe Bloggs	Acme Inc	Tools	Hammer	10.00	Acqusition
1/10/2020	Joe Bloggs	Acme Inc	Tools	Hammer	10.00	Acqusition
1/10/2020	Joe Bloggs	Acme Inc	Tools	Spanner	14.00	Acqusition
1/10/2020	Joe Bloggs	Acme Inc	Tools	Wrench	18.00	Acqusition
1/10/2020	Frank Mitchell	Bits n Bobs	Tools	Hammer	9.00	Wholesale
1/10/2020	Frank Mitchell	Bits n Bobs	Tools	Hammer	9.00	Wholesale
1/10/2020	Jane Doe	Fluff n Stuff	Tools	Hammer	10.00	Acqusition
2/10/2020	Mary Smith	Empire Inc	Fasteners	Bolt	0.50	Retention
2/10/2020	Mary Smith	Empire Inc	Fasteners	Bolt	0.50	Retention
2/10/2020	Mary Smith	Empire Inc	Fasteners	Bolt	0.50	Retention
5/10/2020	Mary Smith	Empire Inc	Fasteners	Bolt	0.50	Retention
6/10/2020	Joe Bloggs	Acme Inc	Tools	Spanner	14.00	Acqusition
6/10/2020	Mary Smith	Empire Inc	Tools	Hammer	10.00	Acqusition
6/10/2020	Joe Bloggs	Acme Inc	Tools	Hammer	10.00	Acqusition
6/10/2020	Joe Bloggs	BigStuff Inc	Tools	Hammer	10.00	Acqusition
8/10/2020	Mary Smith	Empire Inc	Tools	Hammer	10.00	Retention
8/10/2020	Mary Smith	Empire Inc	Tools	Hammer	10.00	Retention
8/10/2020	Mary Smith	Empire Inc	Tools	Hammer	10.00	Retention
8/10/2020	Mary Smith	Smasher	Tools	Hammer	10.00	Retention
8/10/2020	Mary Smith	Basher Global	Tools	Hammer	10.00	Retention
8/10/2020	Mary Smith	Crasher & Co	Tools	Hammer	10.00	Retention
9/10/2020	Joe Bloggs	Acme Inc	Fasteners	Bolt	0.50	Acqusition
13/10/2020	Sharon Jeffries	Adventureworks	Tools	Hammer	9.00	Wholesale
13/10/2020	Sharon Jeffries	Adventureworks	Tools	Hammer	9.00	Wholesale
13/10/2020	Sharon Jeffries	Adventureworks	Fasteners	Bolt	0.40	Wholesale
13/10/2020	Sharon Jeffries	Adventureworks	Fasteners	Bolt	0.40	Wholesale
13/10/2020	Jane Doe	Fluff n Stuff	Fasteners	Bolt	0.50	Acqusition
13/10/2020	Jane Doe	Fluff n Stuff	Fasteners	Bolt	0.50	Acqusition
13/10/2020	Jane Doe	Fluff n Stuff	Fasteners	Bolt	0.50	Acqusition
13/10/2020	Jane Doe	Fluff n Stuff	Fasteners	Bolt	0.50	Acqusition
13/10/2020	Jane Doe	Fluff n Stuff	Fasteners	Bolt	0.50	Acqusition

I created the following measure to assist with applying the sales promotion rules (but without the points rules per customer / product etc):

Promotion Points =
CALCULATE (
    COUNT ( SalesData[Product] ),
    FILTER (
        SalesData,
         ( ( SalesData[Product] = "Hammer"
            || SalesData[Product] = "Bolt" )
            && SalesData[Team] <> "Wholesale" )
    )
)

This then creates the following matrix which is close to the desired result:

Based on the sales promotion rules - the actual points should be as follows:

Jane Doe = 11 Points (10 for Bolts and 1 for Hammers)

Joe Bloggs = 6 Points (2 for Bolts and 3 for Hammers (Capped) for Acme, and 1 for Hammers for BigStuff)

Mary Smith = 14 Points (1 Hammer for Basher, 1 Hammer for Crasher, 8 Points for Bolts for Empire with 3 Points for Hammers (Capped) for Empire and 1 Hammer for Smasher)

Can anyone please advise how the DAX Measure for "Promotion Points" above could be edited to cater for the caps per customer and per product (as laid out in the promotion rules)?

Thanks in Advance!!

[mahoneypat · Accepted solution]

Here is a measure expression that gets your desired results in a table visual with the SalesRep column.

Promotion Points =
SUMX (
    VALUES ( SalesData[Customer] ),
    VAR hammers =
        CALCULATE (
            COUNT ( SalesData[Date] ),
            SalesData[Team] <> "Wholesale",
            SalesData[Product] = "Hammer"
        )
    VAR bolts =
        CALCULATE (
            COUNT ( SalesData[Date] ),
            SalesData[Team] <> "Wholesale",
            SalesData[Product] = "Bolt"
        )
    VAR hammerpoints =
        IF (
            hammers > 3,
            3,
            hammers
        )
    VAR boltpoints =
        IF (
            bolts * 2 > 10,
            10,
            bolts * 2
        )
    RETURN
        hammerpoints + boltpoints
)

Regards,

Pat

[JadeDoherty]

Thank you very much @mahoneypat !

That looks to do the trick, and appears simple enough that I can even understand it... 🙂  You my friend are a rockstar!