Get sample to cover at least certain % over the total

[Angie_]

Hi

I have a list of items to select for sample. The sample creteria is to cover at least 40% of the total.

So from the list, I should cover at least 2 items.

May I know what is the correct way so that the 2nd item will also show "Sample selected" in the "Review Creteria" column.

Thank you.

[v-frfei-msft]

Hi @Angie_ ,

To use this formula as below.

Review Creteria = 
VAR ranki =
    RANKX ( Table1, Table1[accum],, ASC, DENSE )
RETURN
    IF ( Table1[accum]<=0.4 || ranki <= 2, "Sample selected", BLANK () )

[Angie_]

Hi Frank

Thanks for the solution. I would like to ask something further.

What if every month I have difference amount of sample to be selected, for example it might need more than 2 samples to cover at least 40%. Is there any formula that can auto detect to make sure I cover at least 40% of the total as my sample?

Thank you.

[v-frfei-msft · Accepted solution]

Hi @Angie_ ,

Review Creteria = 
VAR ranki =
    RANKX ( FILTER(Table1,Table1[accum]>0.4), Table1[accum],, ASC, DENSE )
RETURN
    IF ( Table1[accum]<=0.4 || ranki =1, "Sample selected", BLANK () )