Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
New Power BI user.
Tried some examples found on the web but I cannot seem to apply them to this scenario.
I have a dataset of purchases for various branches around the United States. I am looking to extract a percentage of them for auditing purposes. More precisely, a percentage by state. For example:
The states have the following number of records:
Alabama 1000
Nebraska 250
California 3750
I want to extract 10% of the records. So
100 of Alabama
25 of Nebraska
375 for California
I have tried creating a column using Number.Random & Number.RandomBetween but every record gets the same random number so I can't use it to select say "the top 10%".
Any guidance would be greatly appreciated.
Solved! Go to Solution.
RAND function return a random value for each row. Please refer the screenshot below.
Regards,
Charlie Liao
Perhaps look at Table.Range
https://msdn.microsoft.com/en-us/library/mt260780.aspx
Also, maybe Table.SelectRows and provide a condition that the row index mod 10 is 0?
https://msdn.microsoft.com/en-us/library/mt260810.aspx
Thank you smoupre.
These have promise. But I need to assign a random value to each record first. I think I need to create a new column and assign a random value to each record. Tried using Number.Random but it provides the same value to each row. Once, I have the values assigned your suggestion will be a good next step so I appreciate that. I may have to go the DAX route.
Appreciate your help.
RAND function return a random value for each row. Please refer the screenshot below.
Regards,
Charlie Liao
Thank you, Charlie.
I was actually looking for a solution to part of the query step which is probably not the correct way of thinking.
I appreciate your feedback.
Brian
Have a look at this post, in particular Colin Banfield's reply:
Hmm, you could do this in DAX if you perhaps have a query by state and then create a calculated table where you grab a number of records less than some index. So, in Alabama create a calculated table of and index <= 100. Kind of hacky. Will try to think of a more elegant solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |