Target Sales Table with Relationship to SQL Table

[jarett]

I have an excel file in SharePoint that contains our quarterly and annual target sales per sales person(SalesTargets_new), we would like to add monthly targets. Within my Power BI report we have a few different visual showing sales progress and we need the filters to adjust the sales figures accordingly on all the visuals. In the "SalesTargets_new" table we created a column with the year and quarter concatenated in order to achieve this. I need to add another column for the monthly targets now in my source file "SalesTargets_new", and I am not seeing clearly on how to accomplish this.

Here is a clip from the source file

SalesTargets_new.xlsx
	A	B	C	D	E
1	SalesPerson	Year	Quarter	Target	AnnualTarget
2	Action House Accounts	2024	1	$34,745.	$150,000.
3	Action House Accounts	2024	2	$38,122.	$150,000.
4	Action House Accounts	2024	3	$36,985.	$150,000.
5	Action House Accounts	2024	4		$150,000.
6	Arnie Alexander	2024	1	$567,506.	$2,450,000.
7	Arnie Alexander	2024	2	$622,659.	$2,450,000.
8	Arnie Alexander	2024	3	$604,084.	$2,450,000.
9	Arnie Alexander	2024	4		$2,450,000.
10	Bryan Lipari	2024	1	$451,688.	$1,950,000.
11	Bryan Lipari	2024	2	$495,586.	$1,950,000.
12	Bryan Lipari	2024	3	$480,802.	$1,950,000.
13	Bryan Lipari	2024	4		$1,950,000.
Sheet1

What my table looks like in power BI

Example of what the report looks like and I want to add another gauge visual for the monthly sales progress if a month filter is selected.

 

[jarett]

Here is my relationships

 

[RoryA]

You don't have months in the data, so I'm not sure how you plan to use a monthly target?

 

[jarett]

You don't have months in the data, so I'm not sure how you plan to use a monthly target?

Correct, all I have is the MonthMapping table to show a visual of the months in my matrix visual, but I know I need to add the months in the "SalesTargets_new" data, just not sure the best way.

 

[jarett]

You don't have months in the data, so I'm not sure how you plan to use a monthly target?

I might of been thinking of this wrong, I was trying to include monthly targets in the same source document. If I have a separate document for the monthly targets that should work for including that data in other visuals, correct?

Monthly Budget_2024 Final.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Quarterly Sales Targets
2	2024
3
4																	YTD
5	Sales Person	FY	January	February	March	April	May	June	July	August	September	October	November	December	Total		January	February	March	April	May	June	July	August	September	October	November	December
6	House Accounts	150,000	11,065	11,536	12,145	12,808	12,868	12,446	12,113	12,071	12,801	13,006	13,235	13,908	150,000		11,065	22,601	34,745	47,553	60,421	72,867	84,980	97,051	109,852	122,858	136,092	150,000
7	Irma Gaines	750,000	55,326	57,678	60,723	64,039	64,341	62,230	60,566	60,355	64,003	65,028	66,174	69,538	750,000		55,326	113,003	173,726	237,765	302,106	364,336	424,902	485,257	549,260	614,288	680,462	750,000
8	Stacey Arceneaux	1,600,000	118,029	123,046	129,542	136,617	137,260	132,758	129,207	128,757	136,540	138,726	141,171	148,349	1,600,000		118,029	241,074	370,616	507,233	644,493	777,250	906,458	1,035,215	1,171,754	1,310,481	1,451,651	1,600,000
9	Arnie Alexander	2,450,000	180,731	188,413	198,361	209,195	210,179	203,285	197,849	197,159	209,076	212,425	216,167	227,159	2,450,000		180,731	369,145	567,506	776,700	986,879	1,190,165	1,388,013	1,585,172	1,794,249	2,006,674	2,222,841	2,450,000
Quarterly Sales Targets

Cell Formulas
Range	Formula
B6	B6	=+'Sales Budgets 2024'!B6
C6:N9	C6	=+'2024 Budget'!D$25/'2024 Budget'!$P$25*'Quarterly Sales Targets'!$B6
O6:O9	O6	=SUM(C6:N6)
B7:B9	B7	=+'Sales Budgets 2024'!B8
Q6:Q9	Q6	=+C6
R6:AB9	R6	=SUM($C6:D6)