Forum Discussion

jasontlambert's avatar
jasontlambert
Frequent Visitor
8 years ago

Average weekly sales

I have sales data in weekly buckets.  I need to calculate two measures using the sales data.  The first measure is to calculate each week's total sales and divide by the average weekly sales for that...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi jasontlambert,

 

You can use year and weeknum as parameter to filter your table, then calculate on the filtered records to get weekly average.

Sample measure:

Week AVG =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
            && WEEKNUM ( [Date], 1 ) = WEEKNUM ( MAX ( [Date] ), 1 )
    ),
    [Amount]
)

 

 

Regards,

Xiaoxin Sheng

  • jasontlambert's avatar
    jasontlambert
    Frequent Visitor
    8 years ago

    Anonymous, thank you, but that's not quite what I'm trying to get.  If it helps, I have added a more detailed screenshot of my data in excel, along with formulas from Excel that gives the results I am trying to get. 

     

    I want to take each week's sales data and divide it by the average for the year.  My total sales for 2015 is 80,190.  If you divide this by 52 (the number of weeks), you get 1,572.35.  My week 1 sales are 1,014.  If you divide 1,014 by 1,572.35 you get 0.6401 as my desired output.  

     

    I also have lots of products that I have sales for and will be switching between products in a slicer or filter.  The formula you provided was giving the same results for all of the products, no matter which one I filtered on.  

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Share the link from where i can download your Excel file (the last one where you have shown an illustration).

      • jasontlambert's avatar
        jasontlambert
        Frequent Visitor
        8 years ago

        Ashish_Mathur, I don't have a good way to share a link, so I've pasted the data below.  Let me know if this doesn't work.  

         

        Measure 1 = E2/AVERAGEIFS($E:$E,$D:$D,D2)

        Measure 2 = AVERAGEIFS($F:$F,$C:$C,C2)

         

        ProductDatefiscal week numyearSalesMeasure 1Measure 2
        ASaturday, January 3, 20151201510140.64010.6464
        ASaturday, January 10, 20152201511370.71780.7294
        ASaturday, January 17, 20153201520961.32311.3342
        ASaturday, January 24, 20154201513380.84460.8524
        ASaturday, January 31, 20155201518441.16411.1742
        ASaturday, February 7, 20156201524591.55231.5673
        ASaturday, February 14, 20157201513790.87050.8771
        ASaturday, February 21, 20158201511370.71780.7262
        ASaturday, February 28, 20159201524981.57691.5871
        ASaturday, March 7, 201510201519191.21141.2253
        ASaturday, March 14, 201511201522121.39641.4077
        ASaturday, March 21, 20151220159000.56810.5748
        ASaturday, March 28, 201513201517941.13251.1434
        ASaturday, April 4, 201514201514530.91720.9282
        ASaturday, April 11, 201515201512320.77770.7885
        ASaturday, April 18, 201516201522231.40331.4119
        ASaturday, April 25, 201517201514920.94190.9510
        ASaturday, May 2, 201518201520231.27711.2835
        ASaturday, May 9, 201519201514080.88880.8966
        ASaturday, May 16, 201520201520651.30361.3095
        ASaturday, May 23, 20152120158890.56120.5698
        ASaturday, May 30, 201522201515690.99051.0002
        ASaturday, June 6, 201523201522851.44241.4522
        ASaturday, June 13, 201524201512680.80040.8081
        ASaturday, June 20, 20152520157350.46400.4712
        ASaturday, June 27, 201526201523141.46081.4720
        ASaturday, July 4, 20152720155640.35600.3637
        ASaturday, July 11, 201528201524361.53781.5480
        ASaturday, July 18, 201529201517941.13251.1442
        ASaturday, July 25, 201530201514910.94120.9496
        ASaturday, August 1, 201531201511110.70130.7085
        ASaturday, August 8, 20153220158050.50820.5145
        ASaturday, August 15, 201533201516371.03341.0433
        ASaturday, August 22, 20153420155300.33460.3445
        ASaturday, August 29, 201535201522921.44691.4554
        ASaturday, September 5, 201536201523101.45821.4627
        ASaturday, September 12, 201537201520641.30291.3081
        ASaturday, September 19, 201538201513510.85280.8575
        ASaturday, September 26, 20153920159140.57700.5840
        ASaturday, October 3, 201540201523081.45701.4646
        ASaturday, October 10, 201541201519951.25941.2651
        ASaturday, October 17, 201542201523391.47651.4845
        ASaturday, October 24, 201543201515500.97850.9878
        ASaturday, October 31, 20154420159580.60480.6099
        ASaturday, November 7, 201545201516891.06621.0716
        ASaturday, November 14, 201546201515690.99050.9973
        ASaturday, November 21, 201547201513320.84080.8490
        ASaturday, November 28, 20154820157030.44380.4474
        ASaturday, December 5, 20154920158500.53660.5394
        ASaturday, December 12, 20155020157920.50000.5056
        ASaturday, December 19, 201551201521231.34021.3438
        ASaturday, December 26, 201552201521841.37871.3818
        ASaturday, January 2, 20161201610150.64300.6464
        ASaturday, January 9, 20162201611610.73550.7294
        ASaturday, January 16, 20163201621191.34241.3342
        ASaturday, January 23, 20164201613500.85520.8524
        ASaturday, January 30, 20165201618561.17581.1742
        ASaturday, February 6, 20166201624841.57361.5673
        ASaturday, February 13, 20167201613860.87800.8771
        ASaturday, February 20, 20168201611410.72280.7262
        ASaturday, February 27, 20169201625171.59451.5871
        ASaturday, March 5, 201610201619431.23091.2253
        ASaturday, March 12, 201611201622291.41211.4077
        ASaturday, March 19, 20161220169060.57400.5748
        ASaturday, March 26, 201613201618061.14411.1434
        ASaturday, April 2, 201614201614780.93630.9282
        ASaturday, April 9, 201615201612530.79380.7885
        ASaturday, April 16, 201616201622301.41271.4119
        ASaturday, April 23, 201617201615110.95720.9510
        ASaturday, April 30, 201618201620261.28351.2835
        ASaturday, May 7, 201619201614200.89960.8966
        ASaturday, May 14, 201620201620671.30941.3095
        ASaturday, May 21, 20162120169020.57140.5698
        ASaturday, May 28, 201622201615931.00921.0002
        ASaturday, June 4, 201623201622891.45011.4522
        ASaturday, June 11, 201624201612820.81210.8081
        ASaturday, June 18, 20162520167560.47890.4712
        ASaturday, June 25, 201626201623201.46971.4720
        ASaturday, July 2, 20162720165760.36490.3637
        ASaturday, July 9, 201628201624551.55521.5480
        ASaturday, July 16, 201629201618111.14731.1442
        ASaturday, July 23, 201630201614980.94900.9496
        ASaturday, July 30, 201631201611320.71710.7085
        ASaturday, August 6, 20163220168240.52200.5145
        ASaturday, August 13, 201633201616591.05101.0433
        ASaturday, August 20, 20163420165550.35160.3445
        ASaturday, August 27, 201635201623111.46401.4554
        ASaturday, September 3, 201636201623161.46721.4627
        ASaturday, September 10, 201637201620731.31321.3081
        ASaturday, September 17, 201638201613610.86220.8575
        ASaturday, September 24, 20163920169330.59110.5840
        ASaturday, October 1, 201640201623241.47231.4646
        ASaturday, October 8, 201641201620061.27081.2651
        ASaturday, October 15, 201642201623561.49251.4845
        ASaturday, October 22, 201643201615740.99710.9878
        ASaturday, October 29, 20164420169710.61510.6099
        ASaturday, November 5, 201645201617001.07701.0716
        ASaturday, November 12, 201646201615851.00410.9973
        ASaturday, November 19, 201647201613530.85710.8490
        ASaturday, November 26, 20164820167120.45110.4474
        ASaturday, December 3, 20164920168560.54230.5394
        ASaturday, December 10, 20165020168070.51120.5056
        ASaturday, December 17, 201651201621271.34751.3438
        ASaturday, December 24, 201652201621861.38481.3818
        ASaturday, December 31, 20165320165610.35540.3554
        ASaturday, January 7, 20171201710330.65610.6464
        ASaturday, January 14, 20172201711570.73490.7294
        ASaturday, January 21, 20173201721051.33701.3342
        ASaturday, January 28, 20174201713500.85750.8524
        ASaturday, February 4, 20175201718621.18271.1742
        ASaturday, February 11, 20176201724811.57591.5673
        ASaturday, February 18, 20177201713900.88290.8771
        ASaturday, February 25, 20178201711620.73810.7262
        ASaturday, March 4, 20179201725031.58981.5871
        ASaturday, March 11, 201710201719421.23351.2253
        ASaturday, March 18, 201711201722271.41451.4077
        ASaturday, March 25, 20171220179170.58250.5748
        ASaturday, April 1, 201713201718161.15351.1434
        ASaturday, April 8, 201714201714660.93120.9282
        ASaturday, April 15, 201715201712500.79400.7885
        ASaturday, April 22, 201716201722351.41961.4119
        ASaturday, April 29, 201717201715020.95400.9510
        ASaturday, May 6, 201718201720311.29001.2835
        ASaturday, May 13, 201719201714190.90130.8966
        ASaturday, May 20, 201720201720711.31541.3095
        ASaturday, May 27, 20172120179080.57670.5698
        ASaturday, June 3, 201722201715761.00101.0002
        ASaturday, June 10, 201723201723051.46411.4522
        ASaturday, June 17, 201724201712780.81170.8081
        ASaturday, June 24, 20172520177410.47070.4712
        ASaturday, July 1, 201726201723391.48571.4720
        ASaturday, July 8, 20172720175830.37030.3637
        ASaturday, July 15, 201728201724421.55111.5480
        ASaturday, July 22, 201729201718151.15281.1442
        ASaturday, July 29, 201730201715090.95850.9496
        ASaturday, August 5, 201731201711130.70690.7085
        ASaturday, August 12, 20173220178080.51320.5145
        ASaturday, August 19, 201733201716461.04551.0433
        ASaturday, August 26, 20173420175470.34740.3445