Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Name | Team |
Jo | Team A |
Fred | Team B |
Jackie | Team A |
Mary | Team A |
Kim | Team B |
A table of targets by team:
Team | Grape Sales Target |
Team A | 5% |
Team B | 8% |
And a table of sales:
Name | Job | Main Product | Date | Grape Sale Attempted | Grape Sale Made |
Jo | 1 | Apple | 01/12/2018 | No | No |
Fred | 2 | Apple | 01/12/2018 | Yes | No |
Fred | 3 | Apple | 01/12/2018 | Yes | Yes |
Jackie | 4 | Apple | 01/12/2018 | Yes | Yes |
Mary | 5 | Banana | 01/12/2018 | No | No |
Kim | 6 | Banana | 01/12/2018 | Yes | No |
Kim | 7 | Banana | 01/12/2018 | Yes | No |
Jo | 8 | Banana | 01/12/2018 | Yes | Yes |
Jo | 9 | Apple | 02/12/2018 | No | No |
Fred | 10 | Apple | 02/12/2018 | Yes | No |
Fred | 11 | Apple | 02/12/2018 | Yes | Yes |
Jackie | 12 | Apple | 02/12/2018 | Yes | Yes |
Mary | 13 | Banana | 02/12/2018 | No | Yes |
Kim | 14 | Banana | 02/12/2018 | Yes | Yes |
Kim | 15 | Banana | 02/12/2018 | Yes | Yes |
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:
Team | Target | Count Hitting target | Count Missing Target |
Team A | 5% | 2 | 1 |
Team B | 8% | 2 | 0 |
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!?
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
@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!
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
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.
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
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |