If value of column is less than a set value, then use value in the column but if over use set value

[95Bigbluetoy]

Need help to create a new column or measure (if better option) that will use either the value of a specific column if equal to or less than a certain number, but if greater than a certain number will use a specific value. As you can see in my table below, i have a "Total Hrs" column. If the value of this column is 173.3 or less for any user, then the value of my new column needs to be the value shown in the "Total Hrs" column for that user. But, if the value is greater than 173.3 for a user, then the value of the new column should be 173.3 for that user. The new column "Total Hours" will replace the current "Total Hrs" column and will be used in calculating my percentages columns.

Any and all help is greatly appreciated.

[Nathaniel_C]

Hi @95Bigbluetoy ,

Then delete TH and rename TH1 as TH. Then you don't have to redo other formulas.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[95Bigbluetoy · Accepted solution]

thanks for your reply. i created the TH1 column as you suggested, but the formula is not working as if the Total Hrs is over 173.3, it is still showing the original Total Hrs and not using the set value of 173.3 in the new TH1 column. see below.

[Nathaniel_C]

Hi @95Bigbluetoy ,

Where are you adding the new column? Which software?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[95Bigbluetoy]

i am in power Bi and adding the column under the same table as the Total Hrs column. The Total Hrs column is a measure I created as it adds the 0100 through 0420 column amounts to get the totals for each user.

[Nathaniel_C]

@95Bigbluetoy ,

Not sure what you have going on, but you might check that both columns are formatted to decimals. Also is it possible to put your Total Hours on your original table as an added column? And then use the formula I provided for this picture.  Also may have been hasty in telling you to delete the Total Hours column as it has your formula that is supply the data to the new column.  You should be fine renaming it though.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[95Bigbluetoy]

I think the issue is that the Total Hours column is based on users total hours which in the SQL table is broken down into pay weeks.  The 173.3 was the average monthly total hours based on 40 hrs week for 52 weeks. I edited the formula to use 40 rather than 173.3 and this has gotten me closer to what i am looking for as now a lot of the users show a monthly total for August of 160 hrs (4 weeks x 40 hrs rather than their true total of over 160). there are still a couple of users showing over 160, so will have to figure out why, but all in all your formula works. Thanks for your help.  

[95Bigbluetoy]

I will give your suggestions a try later as i am in the middle of a software update now. will let you know.

[Nathaniel_C]

@95Bigbluetoy 

Also, is PBI providing the ' around the table name?

[95Bigbluetoy]

the table names are straight from the SQL database. i am connecting using a ODBC connection.

[Nathaniel_C]

@95Bigbluetoy ,

Or as a measure,

Format to show decimal amount.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[95Bigbluetoy]

i tried as a measure and it is not working either. see below. TH1m showing all incorrect amounts.