Forum Discussion
Group By or Summarize Help Needed
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 |
4 Replies
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
Community Champion
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)
)
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
