Sample Size from Row Count - #DAX #Stats #RandomSample

[Anonymous]

Hello!

I need to calculate a sample size based on the row count of a filtered table.

I'm trying to automate part of our bad debt estimation process. I currently pull about 20 aging reports, then filter the table, take the row count and put it into an online calculator to get the sample size - then do some more filtering using RANDOM in Excel to get a sample table that is then analyzed for bad debt.

I've pulled all of my reports into P BI and want to use the SAMPLE function to get my table, but I want ot calculate the sample size based on the row count of the filtered table. does anyone know how to do that?

Here are some parameters that I enter into the online calculator...

• Population = row count of >90
• Confidence level 95%
• Proportion .5
• Confidence interval .05

I'm filtering my table by company and aging >90...

Table =

SAMPLE(10, FILTER('Aging Invoice Detail', 'Aging Invoice Detail'[AgingFromDocDate]>89 ||'Aging Invoice Detail'[Entity]= "CDMT"),0)

Thank you!

[V-lianl-msft]

Hi @Anonymous ,

This thread can be able to help you solve such problems

https://community.powerbi.com/t5/Desktop/Confidence-Level-95/td-p/275280 

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

[kentyler]

VAR row_count = countrows(AgingInvoiceDetail)
Table =

SAMPLE(row_count, FILTER('Aging Invoice Detail', 'Aging Invoice Detail'[AgingFromDocDate]>89 ||'Aging Invoice Detail'[Entity]= "CDMT"),0)

if there are filters on the table you may need to wrap the countrows in a calculate()
Calculate(countrows(AgingInvoiceDatil),ALL(AgingInvoiceDetail))

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[Anonymous]

Hi,
Thank you for your reply.
I'm actually looking for a statistical sample size based off of the population of the entire row count... I don't want to use the row count as the sample size.
Also while you're unsolicited feedback is helpful, I don't really think that the community is the place to be posting those things unless they are asked for. I think most of us in the community have probably used some of the same resources. Just a suggestion.
Thank you,
Kim

[kentyler]

Kim

Thanks for your feedback. Statistics is one of my weak points.

Will reconsider my extended tag line.

Ken Tyler

[Anonymous]

yes, stats isn't a strong point for me either... thank you for taking the time to respond.

Kim