Hello. My question relates to a function to generate an average. Basically...
I have a dataset with ~4000 records. Each record has a number or value associated with it. These values typically range from 25-150, and are whole numbers with repeated incidence of each number (i.e. there could be 150 records with a value of '72'.).
I am trying to designate the bottom 5% and bottom 6-10% of the values with a note or flag. Normally, I would create an If() type statement. However, I need to make sure that the records that are the same number do not overlap into both categories. For example, the bottom 5% (~200 records) of values may range from 25-43, but there may be some values of 43 that also fall in the bottom 6%. However, I would need to ensure that all values of 43, in this case, are designated as part of the bottom 5%.
What kind of measure or calculated column could I create to solve this problem? I will be updating the data on a routine basis, so ideally this would be some type of calculated column that could refresh each time new data is entered into the report.
