Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Darko_Giac
Helper II
Helper II

Tracking and Adjusting Targets using Power Query

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

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@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.

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.