Incorrect Measure Total

Hi Ashish_Mathur ,

I have a problem with your solution.

I have an excel file with 3 tables:

a) Fact table with sales transactions

b) Dim table with Customer data

c) Dim table with Sales Personnel data

I'm trying to calculate the commissions per Sales Representative, but in this example, I'm just following an excercise to calculate the excess of $1,000 USD and have a total amount based on this measure. 

My measure, which works perfectly fine for row level but not for the total, is the following:

Excess = 
VAR ExcessAmount = SUM( Sales[Amount] ) - 1000
VAR ExcessTotal = SUMMARIZE( Sales, Slp[Slpcode], "ExcessTot", ExcessAmount )
RETURN
IF(
    HASONEFILTER( Slp[Slpcode] ),
    IF(
        SUM( Sales[Amount] ) < 1000,
        0,
        ExcessAmount
    ),
    SUMX(
        VALUES( Slp[Slpcode] ),
        ExcessAmount
    )
)

Here's my pbix file also.

The total shouls be $6,300

I'd really appreaciate your help, and the Community.

Best regards,

Fernando