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
Little bit difficult to know without seeing your data.
It sounds to me like something you could do in dax, do you have sample data to share?
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