Total Sales and Quota

[LeFennec]

I have quarterly sales targets (Quotas) at the Area level.  What needs to be done to match the quarterly actual sales data to the quarterly quotas?

My Sales Data has the Areas lined up with the Areas in the quotas, but I cannot seem to get the attainment numbers to line up, using the unpivot function to flatten out the table).

I would like to see a table with the Area, Quota, Sales, and attainment % against Quota.

How do I line up my dates in the two tables?  How do I Any guidance on this?

I have a date table linked to the Sales Date.

New	Q1	Q2	Q3	Q4	2017
East	1,950,000	2,134,000	2,018,000	2,716,000	8,818,000
West	963,000	1,121,000	1,099,000	1,477,000	4,660,000
EMEA	2,157,000	2,240,000	2,052,000	2,764,000	9,213,000
APAC	619,000	643,000	589,000	793,000	2,644,000
Worldwide	5,857,453	6,346,240	6,145,875	8,198,433	26,548,001
Renewal	979,444	1,647,356	1,420,664	1,075,078	5,122,543

[Ashish_Mathur]

Hi @LeFennec,

Please share the layout of both tables - Actual and Quotas.

[LeFennec]

Quotas above.  Sales data like this:

Total Sales	Area	Territory	Billing Date	Quarter
$        3,709.44	EMEA	DACH	1/15/2017	Q117
$        6,342.08	West	Northwest	1/9/2017	Q117
$      29,780.65	EMEA	DACH	2/6/2017	Q117
$      30,660.25	APAC	ANZ	3/15/2017	Q117
$      24,369.62	East	Northeast	3/30/2017	Q117
$        3,781.43	EMEA	France	3/30/2017	Q117
$      28,097.44	West	Texas	2/28/2017	Q117
$      29,417.17	EMEA	Medit	1/15/2017	Q117
$      24,404.36	APAC	China	1/9/2017	Q117
$        1,096.71	East	Northeast	2/6/2017	Q117
$      24,492.81	EMEA	UK	3/15/2017	Q117
$      26,379.04	West	Northwest	3/30/2017	Q117
$      20,119.82	EMEA	France	3/30/2017	Q117
$      30,241.95	APAC	China	2/28/2017	Q117
$      22,196.34	East	Southeast	1/15/2017	Q117
$      28,430.71	EMEA	Uk	1/9/2017	Q117
$      30,624.26	West	Texas	2/6/2017	Q117
$      31,307.53	EMEA	DACH	3/15/2017	Q117
$        7,730.61	APAC	ANZ	3/30/2017	Q117
$      11,257.21	EMEA	DACH	2/6/2017	Q117
$      25,712.24	West	Northwest	3/15/2017	Q117
$      26,039.12	EMEA	DACH	3/30/2017	Q117
$        7,016.67	APAC	ANZ	3/30/2017	Q117
$      27,627.18	East	Northeast	2/28/2017	Q117

[Ashish_Mathur]

Hi,

This one took a while to solve.  Download the .pbix file from here.

Hope this helps.

[v-yulgu-msft]

Hi @LeFennec,

If I understand correctly, the table in your post is your desired result, right? You said you want to line up dates in the two tables, what are these two tables? Please share us the sample data stored in your source table (table structure) so that I can test for you.

Best regards,
Yuliana Gu

[LeFennec]

The DB has field Area which aligns to East, West, EMEA, etc.

I want to take the total Sales by Area and create a table using the targets and total sales as results.

Like this:

Q1 Actuals	From DATA	From Targets	Calculation	Calculation
Area	Q1Tot Sales	Q1 Target	Q1 Attain %	Q1 To Go to Target
East	2,211,300	1,950,000	113.4%	-261,300
West	1,014,039	963,000	105.3%	-51,039
EMEA	2,031,894	2,157,000	94.2%	125,106
APAC	492,724	619,000	79.6%	126,276
Other	432,551	-	NA	NA
Worldwide	6,182,508	5,857,453	105.5%	-325,055

The Target table would look like this and populate the Q1 Target column:

	Targets/Quota
Area	Q1	Q2	Q3	Q4	2017
East	1,950,000	2,134,000	2,018,000	2,716,000	8,818,000
West	963,000	1,121,000	1,099,000	1,477,000	4,660,000
EMEA	2,157,000	2,240,000	2,052,000	2,764,000	9,213,000
APAC	619,000	643,000	589,000	793,000	2,644,000
Other	-	-	-	-	-
Worldwide	5,857,453	6,346,240	6,145,875	8,198,433	26,548,001