Forum Discussion

Yucy's avatar
Yucy
Helper I
8 years ago

Random Select with Selective Criteria DAX

Hi, I want to get a subset of data for auditing purpose, I used "SAMPLE" function, which is very easy and helpful. However I need to keep the sample size but filter something out from the sample pool. Is it possible?

 

For example, I have 5 sales agents, they signed 500 contracts, each contract has different revenue. Now I want to audit 50  with revenue greater than 10k' s contract. Agent 1 and 2 signed 100 contracts each, Agent 3 signed 200 contracts, Agent 4 signed 20, and agent 5 signed 80. So the ratio for each agent will be: 20%, 20%, 40%, 4% and 16%. Now I want my sample is 10 contracts from Agent 1 and 2, 20 contracts from Agent3, 2 contracts from Agent 4, and 8 contracts from Agent 5. And all of the sample contracts revenue has to greater than 10k.

 

Thanks

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi Yucy,

     

    "

    However I need to keep the sample size but filter something out from the sample pool. Is it possible?

    "

     

    <--- Could you share some sample data and clarify more details about your requirement and expected results?

     

    Regards,

    Jimmy Tao

    • Yucy's avatar
      Yucy
      Helper I
      8 years ago

      Yes, basically we want to do a contract sample auditing semi-monthly. Usually we will get 5000 contract every 15 days by different sales people. We want to do a ramdom sample audit for those contracts revenue greater than 5k. First, we want to get a ratio of contacts per sales, say sales A signed 2000 contracts of the 5000 total, we want to ramdom audit 10% of his/her 2000 contracts which revenue greater than 5000. Sales B may only signed 200 contracts of the 5000 total, so we only want to audit 20 contracts from his'her 200 total contracts. Make sense?

       

      Thnaks

    • Yucy's avatar
      Yucy
      Helper I
      7 years ago

      Can you still help on this one?  Thanks