Forum Discussion
Solved
Identifying first rows based on a summed value
Hi all, one more problem. I have a table with a date column, # of hours worked, and our normal price per hour. Usually we charge '$X' per hour but for one client we charge them '$Y' for the first 10 ...
Hi mmikemcgregor1 ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create calculated column.
Rankx = RANKX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[Index],,ASC)Sum = SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Rankx]<=EARLIER('Table'[Rankx])),[Work Hours])Need to change Rate/Hr = IF( 'Table'[Sum]<=10,"150","Stay at Normal Rate")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Sorry, should have included a sample data set. The table below is how the data appears in our data pull. So I need to reclassify the first 10 hours each month(regardless of who worked them) to $150/hours and the rest of the time in that month stays at the normal rate.
| Date | Title | Work Hours | Rate/Hr | Total Amt | Need to change Rate/Hr | |
| 7/1/2022 | Manager | 1 | 300 | 300 | 150 | |
| 7/1/2022 | Apprentice | 2 | 200 | 400 | 150 | |
| 7/2/2022 | Manager | 4 | 300 | 1200 | 150 | |
| 7/2/2022 | Apprentice | 1 | 200 | 200 | 150 | |
| 7/3/2022 | Manager | 1 | 300 | 300 | 150 | |
| 7/3/2022 | Apprentice | 1 | 200 | 200 | 150 | |
| 7/4/2022 | Manager | 3 | 300 | 900 | Stay at Normal Rate | |
| 7/4/2022 | Apprentice | 6 | 200 | 1200 | Stay at Normal Rate | |
| 7/5/2022 | Manager | 1 | 300 | 300 | Stay at Normal Rate | |
| 7/5/2022 | Apprentice | 2 | 200 | 400 | Stay at Normal Rate | |
| 7/6/2022 | Manager | 3 | 300 | 900 | Stay at Normal Rate | |
| 7/6/2022 | Apprentice | 2 | 200 | 400 | Stay at Normal Rate | |
| 8/1/2022 | Manager | 2 | 300 | 600 | 150 | |
| 8/1/2022 | Apprentice | 5 | 200 | 1000 | 150 | |
| 8/2/2022 | Manager | 3 | 300 | 900 | 150 | |
| 8/2/2022 | Apprentice | 4 | 200 | 800 | Stay at Normal Rate | |
| 8/3/2022 | Manager | 1 | 300 | 300 | Stay at Normal Rate | |
| 8/3/2022 | Apprentice | 5 | 200 | 1000 | Stay at Normal Rate | |
| 8/4/2022 | Manager | 3 | 300 | 900 | Stay at Normal Rate | |
| 8/4/2022 | Apprentice | 1 | 200 | 200 | Stay at Normal Rate |