Forum Discussion
Compare Last Week's Sum and Past Two Week's Sum Average
thanks for the above calculation - i am using the same formula but how to break it down say by "Category"
below table works fine when used as calulated measure for over all table values. (average 4 weeks)
| Row Labels | Qty | last 4 weeks Avg Qty | last 4 weeks qty |
| 13 | 761368 | 190342 | 761368 |
| 14 | 1223186 | 496138.5 | 1984554 |
| 15 | 373046 | 589400 | 2357600 |
| 16 | 1323902 | 920375.5 | 3681502 |
| 17 | 676313 | 899111.75 | 3596447 |
| 18 | 623604 | 749216.25 | 2996865 |
| 19 | 1494204 | 1029505.75 | 4118023 |
| 20 | 1374620 | 1042185.25 | 4168741 |
| 21 | 1044666 | 1134273.5 | 4537094 |
| 22 | 1042296 | 1238946.5 | 4955786 |
| 23 | 1323468 | 1196262.5 | 4785050 |
but when i use the "Category" breakdown option its not calculating per Category instead shows teh same values as above. Looks like i need to change teh formual a bit but not sure how.
below is the 2nd table:
| Year | Category | WeekNum | Qty | Last 4 Wks Avg qty | Last 4 Wks qty |
| 2017 | 5066-9616 | 13 | 336 | 190342 | 761368 |
| 2017 | 5066-9616 | 14 | 1344 | 496138.5 | 1984554 |
| 2017 | 5066-9616 | 15 | 1456 | 589400 | 2357600 |
| 2017 | 5066-9616 | 17 | 1792 | 899111.75 | 3596447 |
| 2017 | 5066-9616 | 19 | 896 | 1029505.75 | 4118023 |
| 2017 | 5066-9616 | 21 | 1120 | 1134273.5 | 4537094 |
| 2017 | 5066-9616 | 22 | 784 | 1238946.5 | 4955786 |
| 2017 | 5066-9616 | 23 | 1904 | 1196262.5 | 4785050 |
but the actual result i need is as below:
| Row Labels | Qty | last 4wks avg qty | last 4wks qty |
| 13 | 336 | 84 | 336 |
| 14 | 1344 | 420 | 1680 |
| 15 | 1456 | 784 | 3136 |
| 17 | 1792 | 1148 | 4592 |
| 19 | 896 | 672 | 2688 |
| 21 | 1120 | 504 | 2016 |
| 22 | 784 | 700 | 2800 |
| 23 | 1904 | 952 | 3808 |
please help ...
Hi Shalini,
If you try the following formula for RunTot , it should work
RunTot = CALCULATE (
SUM ( 'Data'[Qty]),
FILTER ( ALLEXCEPT( Data,Data[Category]), Data[WeekNum] >= MAX ( 'Data'[WeekNum] ) - 4 &&Data[WeekNum] <= MAX ( 'Data'[WeekNum] ))
)
Replace Data with your table name.
When you put Category in the table output ensure the Category column is sorted in the ascending order of category.
Let me know if you face any issues.
If it works please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
thank you CheenuSing!! That worked !!!
:)
hi CheenuSing,
Just a quick check.. can i use the same logic to add a formula into "Add column" instead of a measure? if so what will it be ?
and also is there a way to combine tables (Table1+Table2+Table3) that you see in the attached screen shot into Table 4, cause i have calculated measues on these and i have filtered the weeks that i need accordengly and now i just want to combine these into 1 table in the visualization page. and this new table 4 should allow me to add more measures.
is there a way to do this?
ideally i need to achive as below:
Part Avaialble Qty Last 4 Wks Shipment Last 4 Wks Avg Shipment 6 Wks DDS 6 Wks Avg DDS Part a 39,636 23,360 5,840 43,003 7,167 Part b 0 0 0 0 0 Part c 53,929 19,674 4,919 37,230 6,205 Part d 0 0 0 0 0 is this the correct way or is there a better way to do this?
Thanks