topic Re: Calculate number needed to obtain Percentage goal in DAX Commands and Tips

Calculate number needed to obtain Percentage goal

ABR002 — Wed, 17 Mar 2021 17:00:10 GMT

First off if this is a double post I'm sorry, I submitted it earlier and it looks like it didn't go through...

This might be more of a mathmatics question, but I figured I'd ask here.

I'm working on a dashboard that summarizes survey results... you know the ones you take when you get your car serviced and the beg you to give them nothing but 10's. In this case it's basically a thumbs up or thumbs down, which should be easier.

I need to calculate how many thumbs up it would take to get them to 98%. Basically I have the following calculated:
Current Score = 94.5%

Target Score = 98%

Percent to Target = 3.5%

And I need:

Number of thumbs up needed to reach target = ????

The denominator is simply the number of surveys returned. The numerator is number of surveys returned minus thumbs down.

Essentially what my issue is say you have 50 surveys and a score of 50%, so each survey is 2% of the total. Your goal is 75%... at first look it would seem you would need 13, but when you add these new surveys in each one now accounts for 1.5873%, meaning 13 would get you to just 68%... how do I account for that?

Re: Calculate number needed to obtain Percentage goal

OwenAuger — Thu, 18 Mar 2021 21:08:12 GMT

ABR002 you're right, this requires a bit of algebra.

Let's define the following variables, with your sample values in bracketrs:

r = number of survey responses so far (50)
p = number of positive survey responses so far (25)
T = target positive proportion as a decimal (0.75 = 75%)
k = additional positive responses required to get to target (assuming only positive received from now on)

Then we need to solve this equation for T:

A little algebra gets you to the solution:

Using your example, if you had 25 positive responses out of 50, to get to a positive proportion of 0.75 would require:

k = ( 0.75 * 50 - 25 ) / ( 1 - 0.75) = 50

So 50 more positive responses would be required.

You can write this as a measure something like this, assuming you have measures for all the input variables:

Additional Positive Responses Required = VAR Target = 0.75 VAR Positive = [Positive Responses] VAR Total = [Total Responses] VAR AdditionalPositiveRequired = ( Target * Total - Positive ) / ( 1 - Target ) RETURN AdditionalPositiveRequired

Regards,

Owen

Re: Calculate number needed to obtain Percentage goal

ABR002 — Thu, 18 Mar 2021 21:57:25 GMT

Thank you so much. Obviously I'm not a math major, so I may have done something a bit wrong. But when I used your example as a solution, it gave me the total number of responses I would need to be at (as in the lowest number of total responses it would be possible to reach the goal,) not the additional number it would need. Luckily I had manually figured the out the number it would need to be at yesterday so I immediatley recoginized the number, and simply subtracted the current positive count from your example and that got the number I was after. Either way thanks, I couldn't have done this without you.

Re: Calculate number needed to obtain Percentage goal

Icky — Sun, 26 Mar 2023 07:34:35 GMT

This worked great! Thank you! however, is there a chance to only show the aggregated positive values only?

Re: Calculate number needed to obtain Percentage goal

NathanHolowitz — Fri, 19 Apr 2024 14:01:59 GMT

Would there be a way, or a formula used, to input something like this into an Excel Spreadsheet, where I could make mulptiple Spreadsheets for each Metric I am looking to track, and calculate, the number of survey responses needed?

Not looking to have all on one sheet, but multiple sheets containing one metric each. I am looking to create a presentation to explain the impact one negative survey can have and the importance of customer service (Like the gentlemen above, I am in the automotive industry and these surveys are vital)