Calculate Column Values Based on Slicer

[dplank]

Hello,

I am needing to create a field for the individuals pay.  It is based on whether the individual had $2.5 million or more in sales for a given month.  So I need a field that would be .0096 of the total amount for the month if the total amount for the month is at or above $2.5 million or .0092 of the total amount if the total amount for the month is below $2.5 million.  I have a slicer for the month and when the user selects different month's, I need this value to update. 

So if user selected June, this value should be $52,627.54 (.0096 * $5,482,035).  If the user selected April, this value should be $17,719.57 (.0092 * $1,926,040).

Below is part of the data from my transaction table.  Is this possible?

Date	Amount	Name
4/5/2018	163,500.00	Troy
4/10/2018	224,000.00	Troy
4/20/2018	261,900.00	Troy
4/23/2018	452,000.00	Troy
4/24/2018	218,000.00	Troy
4/24/2018	151,320.00	Troy
4/24/2018	85,360.00	Troy
4/25/2018	369,960.00	Troy
5/1/2018	241,559.00	Troy
5/2/2018	220,000.00	Troy
5/2/2018	261,250.00	Troy
5/7/2018	391,000.00	Troy
5/8/2018	214,400.00	Troy
5/14/2018	90,000.00	Troy
5/15/2018	432,000.00	Troy
5/17/2018	161,250.00	Troy
5/17/2018	150,350.00	Troy
5/18/2018	252,000.00	Troy
5/24/2018	320,000.00	Troy
5/25/2018	216,000.00	Troy
5/30/2018	113,000.00	Troy
5/30/2018	253,175.00	Troy
5/31/2018	196,000.00	Troy
5/31/2018	336,000.00	Troy
6/1/2018	117,370.00	Troy
6/8/2018	255,500.00	Troy
6/11/2018	233,910.00	Troy
6/11/2018	888,000.00	Troy
6/13/2018	262,500.00	Troy
6/15/2018	296,000.00	Troy
6/18/2018	176,000.00	Troy
6/21/2018	288,000.00	Troy
6/22/2018	326,800.00	Troy
6/22/2018	180,000.00	Troy
6/25/2018	403,750.00	Troy
6/25/2018	266,250.00	Troy
6/26/2018	236,000.00	Troy
6/27/2018	204,155.00	Troy
6/28/2018	204,000.00	Troy
6/28/2018	218,500.00	Troy
6/29/2018	185,500.00	Troy
6/29/2018	274,800.00	Troy
6/29/2018	465,000.00	Troy

Also, another individual is paid based on year to date totals.  So when his year to date total amount exceeds $10 million, I need to increase his pay to .0098 of total amount for the month instead of .0094 of total amount for the month.  The format of the data is just like I posted above.  Is this possible?

Thank you,

Dan

[v-juanli-msft]

Hi @dplank

Bothe your requirements are possible to do, for the second scenario, could you explain what is the year to date total amount ?

For the first requirement, i can achieve what you expected, look at test below.

Create a calculated column "month" and two measures

month = MONTH([Date])

sum1 = CALCULATE(SUM(Sheet2[Amount]),ALLEXCEPT(Sheet2,Sheet2[month]))

sum2 = IF([sum1]>=2500000,[sum1]*0.0096,[sum1]*0.0092)

Best Regards

Maggie

[dplank]

Thank you for these formulas.  They work with what I was asking.  I added the year to your formula too.  What I failed to mention is that there are about 30 sales associates I want to show in a table how much they got paid.  With the formula above, it doesn't filter out by sales associate.  See below for what it looks like with a sample of my data.  It is taking the total of all associates combined.  How can I get it to filter it out so it just includes the sales for each associate on their own line?

For the year to date, I need to keep a running total of amount of sales (column Amount) and when the year to date total hits $10 million, then the pay would be .0098 of the total amount.  So with the data given above, let's say this individual hit $10 million YTD on the 5/24/18 transaction.  His pay should look like this for May: sum(amount up to and including the 5/24 transaction)*.0094 + sum(amount after 5/24 to month end)*.0098.  And then everything for the rest of the year would be at sum(amount)*.0098 for each month.  Does this make sense and is it possible?

This is just for illustrative purposes.  This indivual is not paid both ways; only the first way, but I thought it would be easiest to explain the year to date portion using the same data.

[v-juanli-msft · Accepted solution]

Hi @dplank

As tested with same dataset, I use a differenet condition for the "year to date" total, you can change the value to meet your needs in your scenario.

First, for your first problem, you can modify my formula to show sum based on sales associates.

sum1 =
CALCULATE (
    SUM ( Sheet2[Amount] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[month] ),
        [sales associate] = SELECTEDVALUE ( Sheet2[sales associate] )
    )
)

sum2 = IF([sum1]>=2500000,[sum1]*0.0096,[sum1]*0.0092)

For year to date total, you can use the following formula

running total =
CALCULATE (
    SUM ( Sheet2[Amount] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[sales associate] ),
        [Date] <= MAX ( [Date] )
    )
)


running total 2 = IF([running total]>4659849,[running total]*0.0094,[running total]*0.0098)

 

Best Regards

Maggie