Tracking and Adjusting Targets using Power Query

[Darko_Giac]

Hi all,

I’m currently working with a fairly large data set (a sample of which is provided in the link below).

I am tracking the number of respondents to a survey, and have created a “have/required” report (in the targets report), which tracks how many respondents we have and the number of respondents that were required for a particular group (gender, race, age, etc…)

I have highlighted a few cells in red, as those indicate overages; in other words, we have over collected.

I’m wondering whether there is a way to omit overages, so that it stops tracking at the required number of participants.

For example, if we have 4/3 (4 being the number we’ve collected, and 3 the amount we actually needed), is there a way to stop the process at 3/3, so extra cases don’t get counted in the report?

Basically, I would like cell B21, for example, to read 3/3, even though there are more respondents, I would like to adjust/restrict the quotas to no more than what the denominator is set at, so that the numbers won’t show any overages

In other words, stop counting even though there might be more respondents.

Test Data

Hopefully I’ve explained the issue clearly enough! Any help would be greatly appreciated!

Currently using Office 365 Pro Plus

[v-yuta-msft · Accepted solution]

@Darko_Giac,

To be general, you may create a custom column with IF statement to control the result with M code like pattern below:

Column = if ([collected] / [needed]) > 1 "" else [collected] / [needed]

Community Support Team _ Jimmy Tao

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