The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to 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.
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.
Thanks!