Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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!