Forum Discussion
Calculate Column Values Based on Slicer
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
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.
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