Advanced Rounding Question

[cphughes]

I am creating a timesheet calculator. An employee will enter a number (hours) and the number will be split up by a number of selected timecodes with different percentages. The number of timecodes will constantly change and can range from 1 to 100. 

Below is the table that has hours split up according to the timecode percentages. In this example the number of hours entered is "1". The timecode system requires a minimum of 0.1 hours per timecode  so I have created a measure which is "Hours (Roundup) = ROUNDUP([Raw Hours], 2). This roundup was done in an effort to spread out the time to as many Timecodes as possible while getting them as close to 0.1 as possible.

However, this has produced an "Hours (Roundup)" total of 1.2 instead of the 1.0 entered. This is not shown in the Totals section of the visual but if you add up all of the number it's 1.2.  I need a measure that drops the Timecodes with the lowest percentages until the total reaches 1.0. To be specific, in this example the 3 rows with 3.75%, 5.00%, and 5.00% would be removed from the table so that the "Hours (Roundup) total equals 1.0. Is this possible with DAX?

Please see my data example below for more info:

[cphughes]

Anybody have any ideas on how to tackle my latest question?

[marcelsmaglhaes]

Hey @cphughes

If you want to cut the numbers smaller, you need to set the number of the cut line. For example, I want to cut all the numbers below 0.5. Each line that has a number below 0.5 will be set to zero. Now, if you don't have the cut-off line number, and you want to choose the smaller numbers until "total roundup hours" equals "gross hours", well, basically you need to run an algorithm in Python to do that. 

[cphughes]

Okay I've given up on the previous method. I have a different issue now. When I enter a number, if I don't round the numbers, the values in the columns don't equal up to the correct total. See column on the right below. Please note that I specifically need 0.1 accuracy for this visual.

If I round the numbers to 0.1 accuracy the values match the total but the total doesn't match up with the number entered. (left column) I understand that this is basic limitation of rounding but is there a way to get both the values and the totals to match with 0.1 accuracy? If not, is there a way to add or subtract the difference to or from the rows to get the totals and values to match the amount entered?

[marcelsmaglhaes]

Hey @cphughes 

I don't know if I understood your question, but in Power Query you check if the Raw Hours is lower than 0.06 and set 0 if yes, or roundup if not. See the example bellow. This will sum 0 on visualization.

I put the column Hours Roundup and the SUM was 0.9 (wich is correct). Then I created a measure adding the roundup column and formatted to wholenumber. The total sum was 1.

Let me know if helps in your question.

[cphughes]

Thanks for your reply. I have added a bit more context to my original post but I need the calculation to be done dynamically. The rows in the table will constantly change and I don't want to always omit budgets less than 0.06. This number will change according to the rows that have the smallest percentage. So sometimes it could be 0.003 or even 0.2. I need the measure to automatically determine which timecodes to drop to get to the total hours to match the value of hours entered.

So as you can see in the visual, the total hours entered is 1.0 but since the numbers are rounded up if you add up the "roundup hours" in each row it adds up to 1.2. I need the measure to get me to 1.0 by dynamically dropping the budgets with the smallest percentages.

There may be a better way to do this altogether. I'm just trying to find the best way to get to the calculation.

[marcelsmaglhaes]

So, dynamically means you need have a rule like: drop everthing is smaller than 0.06. It's impossible not define a parameter to put a zero or drop the row.

[cphughes]

I need the measure to drop the timecodes(rows) with the lowest percentages. In my visual the lowest percentage is 3.75% or 0.0375. The measure would drop that row or change it to zero so that it doesn't count towards the total. It will continue dropping the lowest percentage until "total hours roundup" is equal to "raw hours". So in order to get to the goal in the visual (1.0) it would drop 3.75%, 5.00%, and the other 5.00% in order to go from 1.2 hours to 1.0 hours.