Monthly Commission Orders by Salesperson Above Minimum Count

[Anonymous]

I'm working on building a table that shows all Commissioned Orders. Only three columns are being used in the above measures: OrderNumber, OrderDate, & Salesperson. I've included sample data with the measures in a PBI file below. 

-In this example the business defines a commission order as all orders in a given month a salesperson gets above their first 400 orders.

-The commission per order in this example is $3.5 for each order after the first 400 in a month.

I'm currrently using the following measures & am looking for alternatives:

Commission Amount = [Commission Orders]*3.5
Commission Orders =

IF

(

COUNTROWS(VALUES(OrderData[Salesperson])) = 1,

[OrdersOver400],

SUMX(VALUES(OrderData[Salesperson]), [OrdersOver400])

)

OrdersOver400 = if(COUNT(OrderData[OrderNumber]) > 400, COUNT(OrderData[OrderNumber]) - 400, 0)

I'm unable to get the count for commission orders to display correctly when the values are low or 0.

SamplePBIX 

[Tad17]

Can you post an example of one that doesnt calculate correctly? Based on your description the screenshot looks correct.

[Anonymous]

Hi Tad17, 

Take a look at the incorrect total for Commission Orders. 

[Anonymous · Accepted solution]

Found the solution: 

Commission Orders = SUMX(SUMMARIZE(OrderData,OrderData[Salesperson],OrderData[OrderDate].[Month]), [OrdersOver400])