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
Anonymous
Not applicable

Count how many are achieving targets

I'm really struggling with this, I need to count how many people are achieving a target, but my targets vary and my data is not rolled up, but line-by-line sales.

 

I have a calendar table, grouping weeks, months etc, by calendar day, which I won'r demonstrate here

 

I have an employees table:

NameTeam
JoTeam A
FredTeam B
JackieTeam A
MaryTeam A
KimTeam B

 

A table of targets by team:

TeamGrape Sales Target
Team A5%
Team B8%

 

And a table of sales:

NameJobMain ProductDateGrape Sale AttemptedGrape Sale Made
Jo1Apple01/12/2018NoNo
Fred2Apple01/12/2018YesNo
Fred3Apple01/12/2018YesYes
Jackie4Apple01/12/2018YesYes
Mary5Banana01/12/2018NoNo
Kim6Banana01/12/2018YesNo
Kim7Banana01/12/2018YesNo
Jo8Banana01/12/2018YesYes
Jo9Apple02/12/2018NoNo
Fred10Apple02/12/2018YesNo
Fred11Apple02/12/2018YesYes
Jackie12Apple02/12/2018YesYes
Mary13Banana02/12/2018NoYes
Kim14Banana02/12/2018YesYes
Kim15Banana02/12/2018YesYes

 

I have slicers to filter the Main Product and dates, and then this measure working out sales success:

  

Sales % by team = 
DIVIDE(CALCULATE (
    COUNTROWS ( 'Sales' ),
    USERELATIONSHIP ( 'Employees'[Name], 'Sales'[Name] ) /* Need this due to other relationships elsewhere*/,
    KEEPFILTERS('Sales'[Grapes Sales Made] = "Yes")
,CALCULATE (
    COUNTROWS ( 'Sales' ),
    USERELATIONSHIP ( 'Employees'[Name], 'Sales'[Name] ) /* Need this due to other relationships elsewhere*/)
)

 

I have joins between the calendar to the Sales (by Date), Employees to the Sales (by Name) and Employees to the Targets (by Team) table

 

What I now need to be able to do is count the number of employees achieving their own teams targets, (and in this case, the targets and data is all made up as a simplified representation of my data), but the results I want to see are:

 

TeamTargetCount Hitting targetCount Missing Target
Team A5%21
Team B8%20

 

If I then click on Team A, I'd like another table to be able to demonstrate WHO  is hitting or missing target, whether they attempted the sale, etc.

 

The only thing I've found so far is SUMMARIZE, but I can't for the life of me get it to work!

 

HELP VERY MUCH NEEDED, PLEASE!?

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

Hi damouk,

 

"If I then click on Team A, I'd like another table to be able to demonstrate WHO  is hitting or missing target, whether they attempted the sale, etc."

 

<--- I'm afraid dynamic calculate table can't be achieved in power bi. You can create three measures, [Sales % percent by team], [Count Hitting target] and [Count Missing target] and then drag them into one chart.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

@v-yuta-msft wrote:

Hi damouk,

 

"If I then click on Team A, I'd like another table to be able to demonstrate WHO  is hitting or missing target, whether they attempted the sale, etc."

 

<--- I'm afraid dynamic calculate table can't be achieved in power bi. You can create three measures, [Sales % percent by team], [Count Hitting target] and [Count Missing target] and then drag them into one chart.

 

Regards,

Jimmy Tao



Can you support me in creating these measures? I can't make it work, given the table structure I've described!

Anonymous
Not applicable

on your goal output table at the bottom, can you expand that out?  I'm dividing out Total of Grape Sales made over Total sales and getting %'s much higher than 5% and 8%, but I think I may just be missing something

Anonymous
Not applicable

Hi there,

 

Thanks for looking at this. WHat I’ve put here is just a made up example to demonstrate what I’m doing, the real data is millions of records, of which around 5% actually see additional sales of different types of products, hence putting the targets in at that level. Demonstrating the requirement with only a few lines meant it was impossible to reflect that really.

 

if it’s easier to test it, duplicate the records up to enough quantity and change the numbers in my example so that only 1 or 2 people have made any sales.

 

thanks again.

 

 

Anonymous
Not applicable

ah ok. so on last table you had (your goal output) those figures are more less place holders and what the above data should come out to, correct?  Just want to be sure looking at the same things here before I spend too much time on it Smiley Surprised

Anonymous
Not applicable

Yep, exactly that, I just need to return a count of how many are within or outide of the target at each team level. I'd like it that I can also list who on another visual, if I select one, but that's less important than just working out the counts at the moment.

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.