Total Sales and Quota

Question

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

v-yulgu-msft · Answer

Hi&nbsp;LeFennec,
&nbsp;
If I understand correctly, the&nbsp;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?&nbsp;Please share us the sample data stored in your source table (table structure) so that I can test for you.
&nbsp;
Best regards,Yuliana Gu

lefennec · Answer

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 ActualsFrom DATAFrom TargetsCalculationCalculationAreaQ1Tot SalesQ1 TargetQ1 Attain %Q1 To Go to TargetEast&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,211,300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,950,000113.4%-261,300West&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,014,039&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 963,000105.3%-51,039EMEA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,031,894&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,157,00094.2%125,106APAC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 492,724&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 619,00079.6%126,276Other&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 432,551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;NANAWorldwide&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,182,508&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,857,453105.5%-325,055&nbsp;The Target table would look like this and populate the Q1 Target column:&nbsp;Targets/Quota&nbsp;&nbsp;&nbsp;&nbsp;AreaQ1Q2Q3Q42017East&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,950,000&nbsp;&nbsp; 2,134,000&nbsp;&nbsp; 2,018,000&nbsp;&nbsp; 2,716,000&nbsp;&nbsp; 8,818,000West&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 963,000&nbsp;&nbsp; 1,121,000&nbsp;&nbsp; 1,099,000&nbsp;&nbsp; 1,477,000&nbsp;&nbsp; 4,660,000EMEA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,157,000&nbsp;&nbsp; 2,240,000&nbsp;&nbsp; 2,052,000&nbsp;&nbsp; 2,764,000&nbsp;&nbsp; 9,213,000APAC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 619,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 643,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 589,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 793,000&nbsp;&nbsp; 2,644,000Other&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Worldwide5,857,4536,346,2406,145,8758,198,43326,548,001

ashish_mathur · Answer

Hi&nbsp;LeFennec,&nbsp;Please share the layout of both tables - Actual and Quotas.

lefennec · Answer

Quotas above.&nbsp; Sales data like this:&nbsp;Total Sales&nbsp;AreaTerritoryBilling DateQuarter&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,709.44EMEADACH1/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,342.08WestNorthwest1/9/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,780.65EMEADACH2/6/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,660.25APACANZ3/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,369.62EastNortheast3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,781.43EMEAFrance3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28,097.44WestTexas2/28/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,417.17EMEAMedit1/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,404.36APACChina1/9/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,096.71EastNortheast2/6/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,492.81EMEAUK3/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,379.04WestNorthwest3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,119.82EMEAFrance3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,241.95APACChina2/28/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,196.34EastSoutheast1/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28,430.71EMEAUk1/9/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,624.26WestTexas2/6/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31,307.53EMEADACH3/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,730.61APACANZ3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,257.21EMEADACH2/6/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,712.24WestNorthwest3/15/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,039.12EMEADACH3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,016.67APACANZ3/30/2017Q117&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,627.18EastNortheast2/28/2017Q117

ashish_mathur · Answer

Hi,&nbsp;This one took a while to solve.&nbsp; Download the .pbix file from here.&nbsp;Hope this helps.

Forum Discussion

Total Sales and Quota

5 Replies

Related content

exceed quotas

Request Quota reached

Fabric Quotas

Total Sales per Working Days Current Year vs Total Sales per Working Days Previous Year

Help With Semantic Model - Different Sums & Counts for Sales in Specific Counties From Total Sales

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

	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

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