DAX Random Sample with criteria 5% of the volume

[ardianfirman06]

Hi, 

Is there any way to create a random sample with criteria of 5% of the volume from each category

[v-yangliu-msft · Accepted solution]

Hi @ardianfirman06 ,

According to your description You want to create a random sample of the aggregated value(Volume: 0-5%) grouped by a certain column.Right?

Here are the steps you can follow：

(1)This is my test data:

(2)If you want to create a random sample in visual, you can create a measure:

ran_value = SUM(Table1[value])*RAND()*5/100

Then you can put it in your visual like this:

If you don’t want to have the “Total”, you can close it in Format Pane.

You can click

 to recalculate the value of this measure to achieve a random sample.

(3)If you want to create a random sample in table, you can click “new table”.

Table = SELECTCOLUMNS( SUMMARIZE('Table1','Table1'[name],"Sum Value",SUM(Table1[value])) , "name",[name],"Ran Value",[Sum Value] * RAND()*5/100)

Then you can click

to recalculate the table to achieve a random sample, like this:

If this solution is different from your needs, can you share sample data and sample output in table format? Or a ample .pbix file after removing sensitive data. We can better understand the problem and help you.

If you need pbix, please click here.

case_02.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ardianfirman06]

Thank you for this. this is really help.

is there any function that we can use to keep the sample ? so the table of sampling wont changed on every running and increase the sample if there is additional data ingested to the source table.

[amitchandak]

@ardianfirman06 , refer if these can help

https://community.powerbi.com/t5/Desktop/DAX-random-sample/m-p/386571

https://chandoo.org/wp/power-query-random-sample/