Forum Discussion
Solved
bonus - proper range search
Hi,
Can someone help find proper solution?
I have two tabeles . First with turnovers and second with scale I woluld like to find realted range to calculate proper percentage value.
| turnover value start | turnover value end | % |
| 0 | 8000 | 0 |
| 8001 | 12000 | 2 |
| 12001 | 14000 | 3 |
| Name | turnover | bonus % | bonus |
| Emp 1 | 7900 | 0 | 0 |
| Emp 2 | 11000 | 2 | 220 |
| Emp 3 | 13000 | 3 | 390 |
Hi,
In Table2, write these calculated column formulas
Bonus % = CALCULATE(MIN(Table1[%]),FILTER(Table1,Table1[turnover value start]<=EARLIER(Table2[turnover])&&Table1[turnover value end]>=EARLIER(Table2[turnover])))Bonus = Table2[Bonus %]*Table2[turnover]Hope this helps.
4 Replies
- Ashish_Mathur
Super User
Hi,
In Table2, write these calculated column formulas
Bonus % = CALCULATE(MIN(Table1[%]),FILTER(Table1,Table1[turnover value start]<=EARLIER(Table2[turnover])&&Table1[turnover value end]>=EARLIER(Table2[turnover])))Bonus = Table2[Bonus %]*Table2[turnover]Hope this helps.
Thank you.
- Ashish_Mathur
You are welcome.
- AlB
Community Champion
Hi TomaszTub
You need to explain better. Please provide the expected result for the sample data, explaining the rationale behind it.
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
