Sales for only Reps who have reached a certain status at any point

[Anonymous]

Hi,

I have some sales data where I want to focus on those sales reps who hit a Tier 1 status at any point in their tenure.  I think I need two measures to calculate the sales for that rep and the duration that they have been active.

I know I could use a slicer but I really need the space so I was hoping to use two measures and filter the data to only show reps who ever hit tier 1 status (at any point).

I am trying to create a measure using a filter to isolate only those reps but I keep running into the fact that I am using a text field to filter this.   Is there an easy way to do this?  Thanks

Sales Rep	year	Start Date	Old Tier	New Tier	Amount
A	2020	6/1/2020	4	3	1000
B	2021	1/1/2021	3	3	1000
C	2021	1/1/2021	2	3	1000
D	2021	1/1/2021	4	3	1000
A	2022	1/1/2022	3	1	10000
B	2022	1/1/2022	3	2	1000
Result Sales	A				11000
Result Duration	A				1.5 Years

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ,

You could add a card visual ,then put amount column in it.

Then filter the column is"A".

The final show:

As duration, you could create a measure.

Duration = ( CALCULATE(DATEDIFF(MIN('Table'[Start Date]),MAX('Table'[Start Date]),MONTH),FILTER(ALL('Table'),[Sales Rep]="A"))-1)/12 &"Years"

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks!