Group By or Summarize Help Needed

[stlcards]

Hello to all. I am working on a project in Power Pivot in Excel and am stuck on a DAX formula.  I am wanting to calculate total sales for 52, 13 and 4 at a total for each time period.  Once I get that total, that would be used as the denominator in calculating share.  When I used the total formula in DAX, it gave me one combined number all 3 time periods.  

Ideally I want a total for 52 weeks, total for 13 weeks and a total for 4 weeks.

This is not weekly data, the data returns from the provider in 52 weeks at the upc level or 13 weeks.  

UPC	Store Nbr	Time Period	Sales TY	Qty TY	Sales LY	Qty LY	# of Weeks
3944755588	2089	13wk	47.76	12	35.82	9	13
9855634879	2089	13wk	77.7	16	0	0	13
7692636085	2091	13wk	65.78	11	17.94	3	13
5207018231	2091	13wk	0	0	152.32	34	13
951798882.8	2091	13wk	0	0	24.21	4	13
2628763792	2100	13wk	17.92	4	207.04	48	13
7414087558	211	13wk	0	0	27.86	7	13
2375369460	2111	13wk	0	0	220.81	48	13
1259242549	2111	13wk	208.12	44	70.21	15	13
5296235438	2111	13wk	348.6	70	0	0	13
3434508973	212	13wk	11.94	3	0	0	13
7558963200	4332	4wk	0	0	6.27	1	4
10537982254	4332	4wk	49.8	10	232.96	52	4
10368002560	4333	4wk	0	0	734.79	159	4
3332918037	4333	4wk	298.99	63	359.48	76	4
7801012491	4333	4wk	680.46	135	0	0	4
1280186194	4334	4wk	214.14	43	413.34	83	4
3791013954	4334	4wk	9.96	2	0	0	4
1177486626	1004	52wk	0	0	39.94	2	52
11014660927	1087	52wk	39.94	2	99.85	5	52
4830995675	2533	52wk	179.76	12	0	0	52
1675391850	2920	52wk	0	0	65	8	52
1056444583	4565	52wk	0	0	99.85	5	52
602821706.5	5299	52wk	64.97	10	0	0	52
4351060672	649	52wk	39.94	2	39.94	2	52
2829752037	1004	52wk	0	0	62.72	14	52
5951822524	1004	52wk	53.92	20	11.96	2	52
5990812459	1010	52wk	0	0	9.96	2	52
2443077004	1029	52wk	0	0	31.36	7	52

[stlcards]

Here is more detail about what I am after, the data below is how I will like my Pivot Table to look.  The numbers in the 52 week total, 13 wk total and 4 week total are all the same currently, they should not be.  The current formula I have calculating the total is adding all 3 time periods together.  

		52wk	52wk	52wk	52wk	52wk	13wk	13wk	13wk	13wk	13wk	4wk	4wk	4wk	4wk	4wk
GT	State	Sales $ TY	Sales $ LY	Sales $ Chg	Total	Total YA	Sales $ TY	Sales $ LY	Sales $ Chg	Total	Total YA	Sales $ TY	Sales $ LY	Sales $ Chg	Total	Total YA
TOTAL SEGMENT	TX	$6,331,185	$6,179,510	$151,674	$55,759,688	$53,288,792	$1,742,108	$1,520,819	$221,288	$55,759,688	$53,288,792	$591,641	$506,511	$85,131	$55,759,688	$53,288,792
TOTAL SEGMENT	FL	$5,148,103	$5,510,617	-$362,514	$55,759,688	$53,288,792	$1,352,939	$1,278,414	$74,525	$55,759,688	$53,288,792	$458,121	$449,114	$9,007	$55,759,688	$53,288,792
TOTAL SEGMENT	NC	$2,042,500	$2,035,145	$7,355	$55,759,688	$53,288,792	$579,884	$491,880	$88,005	$55,759,688	$53,288,792	$196,376	$158,633	$37,742	$55,759,688	$53,288,792
TOTAL SEGMENT	OK	$2,041,522	$2,130,274	-$88,753	$55,759,688	$53,288,792	$541,058	$509,551	$31,507	$55,759,688	$53,288,792	$182,572	$161,435	$21,136	$55,759,688	$53,288,792
TOTAL SEGMENT	MO	$1,768,492	$1,767,647	$845	$55,759,688	$53,288,792	$470,601	$445,141	$25,460	$55,759,688	$53,288,792	$161,004	$147,262	$13,741	$55,759,688	$53,288,792
TOTAL SEGMENT	VA	$1,750,933	$1,842,432	-$91,499	$55,759,688	$53,288,792	$491,311	$413,293	$78,018	$55,759,688	$53,288,792	$163,141	$128,594	$34,547	$55,759,688	$53,288,792
TOTAL SEGMENT	TN	$1,612,902	$1,845,879	-$232,976	$55,759,688	$53,288,792	$525,410	$442,894	$82,516	$55,759,688	$53,288,792	$183,772	$129,811	$53,961	$55,759,688	$53,288,792
TOTAL SEGMENT	AL	$1,597,105	$1,388,551	$208,554	$55,759,688	$53,288,792	$483,398	$338,295	$145,102	$55,759,688	$53,288,792	$172,618	$108,195	$64,423	$55,759,688	$53,288,792
TOTAL SEGMENT	GA	$1,492,008	$1,147,550	$344,458	$55,759,688	$53,288,792	$438,727	$309,279	$129,448	$55,759,688	$53,288,792	$151,729	$103,218	$48,511	$55,759,688	$53,288,792
TOTAL SEGMENT	AR	$1,481,271	$1,362,224	$119,047	$55,759,688	$53,288,792	$402,724	$336,832	$65,892	$55,759,688	$53,288,792	$136,946	$117,133	$19,813	$55,759,688	$53,288,792
TOTAL SEGMENT	CA	$1,439,303	$1,320,190	$119,112	$55,759,688	$53,288,792	$382,502	$313,354	$69,148	$55,759,688	$53,288,792	$124,112	$106,257	$17,855	$55,759,688	$53,288,792
TOTAL SEGMENT	SC	$1,411,227	$1,524,789	-$113,562	$55,759,688	$53,288,792	$371,153	$350,944	$20,209	$55,759,688	$53,288,792	$119,551	$110,682	$8,869	$55,759,688	$53,288,792
TOTAL SEGMENT	LA	$1,367,964	$1,392,275	-$24,312	$55,759,688	$53,288,792	$364,850	$344,975	$19,874	$55,759,688	$53,288,792	$125,556	$106,566	$18,990	$55,759,688	$53,288,792
TOTAL SEGMENT	IL	$1,073,622	$1,058,600	$15,022	$55,759,688	$53,288,792	$306,912	$270,587	$36,325	$55,759,688	$53,288,792	$104,833	$90,834	$13,999	$55,759,688	$53,288,792
TOTAL SEGMENT	AZ	$1,067,681	$1,136,144	-$68,463	$55,759,688	$53,288,792	$305,973	$230,605	$75,368	$55,759,688	$53,288,792	$109,876	$69,162	$40,713	$55,759,688	$53,288,792
TOTAL SEGMENT	WA	$1,001,709	$1,000,070	$1,639	$55,759,688	$53,288,792	$268,503	$246,689	$21,814	$55,759,688	$53,288,792	$83,574	$77,877	$5,696	$55,759,688	$53,288,792
TOTAL SEGMENT	OH	$956,614	$867,180	$89,434	$55,759,688	$53,288,792	$265,601	$227,632	$37,969	$55,759,688	$53,288,792	$89,937	$77,127	$12,810	$55,759,688	$53,288,792
TOTAL SEGMENT	WI	$827,115	$786,617	$40,499	$55,759,688	$53,288,792	$232,129	$192,858	$39,271	$55,759,688	$53,288,792	$80,215	$64,881	$15,335	$55,759,688	$53,288,792
TOTAL SEGMENT	MI	$674,287	$547,291	$126,996	$55,759,688	$53,288,792	$199,236	$133,395	$65,841	$55,759,688	$53,288,792	$65,889	$40,608	$25,281	$55,759,688	$53,288,792
TOTAL SEGMENT	OR	$638,331	$655,572	-$17,241	$55,759,688	$53,288,792	$180,916	$150,396	$30,520	$55,759,688	$53,288,792	$57,704	$47,102	$10,601	$55,759,688	$53,288,792
TOTAL SEGMENT	ID	$595,809	$610,661	-$14,852	$55,759,688	$53,288,792	$172,064	$137,881	$34,184	$55,759,688	$53,288,792	$56,573	$44,949	$11,624	$55,759,688	$53,288,792
TOTAL SEGMENT	IN	$553,504	$588,752	-$35,248	$55,759,688	$53,288,792	$144,680	$140,320	$4,360	$55,759,688	$53,288,792	$50,063	$47,741	$2,322	$55,759,688	$53,288,792
TOTAL SEGMENT	NE	$518,522	$508,972	$9,550	$55,759,688	$53,288,792	$144,977	$133,542	$11,435	$55,759,688	$53,288,792	$47,836	$46,497	$1,340	$55,759,688	$53,288,792
TOTAL SEGMENT	NM	$446,760	$442,230	$4,529	$55,759,688	$53,288,792	$132,108	$109,696	$22,412	$55,759,688	$53,288,792	$44,474	$34,439	$10,035	$55,759,688	$53,288,792
TOTAL SEGMENT	MT	$404,759	$330,416	$74,344	$55,759,688	$53,288,792	$118,103	$87,171	$30,932	$55,759,688	$53,288,792	$37,370	$27,428	$9,942	$55,759,688	$53,288,792
TOTAL SEGMENT	NV	$391,706	$414,433	-$22,727	$55,759,688	$53,288,792	$106,165	$98,729	$7,436	$55,759,688	$53,288,792	$35,048	$30,092	$4,956	$55,759,688	$53,288,792
TOTAL SEGMENT	ME	$387,954	$407,495	-$19,541	$55,759,688	$53,288,792	$106,659	$91,245	$15,414	$55,759,688	$53,288,792	$34,947	$27,466	$7,481	$55,759,688	$53,288,792
TOTAL SEGMENT	IA	$357,861	$379,373	-$21,513	$55,759,688	$53,288,792	$99,171	$92,818	$6,353	$55,759,688	$53,288,792	$34,243	$32,306	$1,937	$55,759,688	$53,288,792
TOTAL SEGMENT	WV	$303,972	$288,828	$15,144	$55,759,688	$53,288,792	$81,410	$76,410	$5,000	$55,759,688	$53,288,792	$29,581	$23,510	$6,071	$55,759,688	$53,288,792
TOTAL SEGMENT	SD	$284,626	$288,478	-$3,852	$55,759,688	$53,288,792	$85,435	$69,226	$16,209	$55,759,688	$53,288,792	$27,571	$21,653	$5,918	$55,759,688	$53,288,792
TOTAL SEGMENT	AK	$135,059	$136,260	-$1,201	$55,759,688	$53,288,792	$33,901	$31,198	$2,703	$55,759,688	$53,288,792	$10,258	$9,488	$770	$55,759,688	$53,288,792
TOTAL SEGMENT	MN	$120,708	$110,271	$10,436	$55,759,688	$53,288,792	$31,816	$22,672	$9,144	$55,759,688	$53,288,792	$11,213	$6,022	$5,191	$55,759,688	$53,288,792
TOTAL SEGMENT	KY	$104,021	$118,423	-$14,403	$55,759,688	$53,288,792	$29,426	$28,112	$1,314	$55,759,688	$53,288,792	$8,829	$8,219	$610	$55,759,688	$53,288,792
TOTAL SEGMENT	HI	$82,577	$78,505	$4,072	$55,759,688	$53,288,792	$20,460	$19,585	$875	$55,759,688	$53,288,792	$6,709	$6,860	-$151	$55,759,688	$53,288,792
TOTAL SEGMENT	ND	$78,548	$78,171	$377	$55,759,688	$53,288,792	$24,763	$18,750	$6,013	$55,759,688	$53,288,792	$7,569	$5,773	$1,796	$55,759,688	$53,288,792
TOTAL SEGMENT	VT	$62,909	$33,114	$29,795	$55,759,688	$53,288,792	$21,859	$8,624	$13,234	$55,759,688	$53,288,792	$7,168	$2,832	$4,336	$55,759,688	$53,288,792
TOTAL SEGMENT	MA	$52,482	$17,057	$35,425	$55,759,688	$53,288,792	$21,457	$5,369	$16,088	$55,759,688	$53,288,792	$7,646	$1,599	$6,046	$55,759,688	$53,288,792
TOTAL SEGMENT	DC	$37,447	$38,503	-$1,056	$55,759,688	$53,288,792	$9,943	$10,316	-$373	$55,759,688	$53,288,792	$3,234	$3,599	-$365	$55,759,688	$53,288,792
TOTAL SEGMENT	NH	$4,546	$4,114	$432	$55,759,688	$53,288,792	$1,456	$1,369	$87	$55,759,688	$53,288,792	$629	$270	$360	$55,759,688	$53,288,792
TOTAL SEGMENT	PA	$50	$1,523	-$1,473	$55,759,688	$53,288,792	$25	$1,166	-$1,141	$55,759,688	$53,288,792	$0	$0	$0	$55,759,688	$53,288,792
TOTAL SEGMENT	WY	$39	$696	-$657	$55,759,688	$53,288,792	$39	$193	-$154	$55,759,688	$53,288,792	$0	$0	$0	$55,759,688	$53,288,792
TOTAL SEGMENT Total		$40,647,688	$40,374,818	$272,870	$55,759,688	$53,288,792	$11,291,852	$9,732,225	$1,559,626	$55,759,688	$53,288,792	$3,820,128	$3,181,748	$638,380	$55,759,688	$53,288,792

What I am trying to solve for is a $ share number.  I would divide the 52wk Sales  $ TY number for TX, into the 52wk Total Sales number as an example.  The 52wk total sales number should be appoximately $40MM

[stlcards]

The Pivot table would look something like this.  Trying to calculate the total $ of each time period, so that I can then take that number, use it as the denominator in a share change equation by state.  

TX $ Sales/52 week total $ number

State	TY Sales	$ Trend	$ Share	Share Change
TX	222,222	+4%	14.2%	+0.3
MO	111,111	-3%	8%	-0.2

[tamerj1]

@stlcards 

I don't have enough information to build a complete measure formula but I as per my understanding you need to do something like this

SUMX (

VALUES ( 'Table'[Time Period] ),

VAR TotalPeriod = CALCULATE ( SUM ( 'Table'[Sales TY] ) )

RETURN

(Your share change equation)

)

[tamerj1]

Hi @stlcards 

Would you pleasee clarify how would you like your pivot table to look like? Can you advise the expected results for the given sample of data?