Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jasontlambert
Frequent Visitor

Average weekly sales

‎11-10-2017 09:42 AM

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 year.  My sales data goes back to 2015.  In my image below, the first week of 2015 has 1,603 in sales.  The total sales in 2015 is 104,806 and there are 52 weeks of data.  So 1,603 / (104,806/52) = 0.795.  Week 2 total is 1,114, so the measure should calculate 0.5527.  When the values switch to 2016, the measure needs to begin factoring in the average for 2016.  One other issue is that for 2017, we only have 45 weeks of YTD sales, so the measure must calculate the average correctly with the 45 weeks instead of 52, and when I update the data next week it should change to 46, etc. 

 

The second measure that I need is to be able to calculate the average of Week 1 from each year, the average of week 2 for each year, etc.  In the first step, the output from week 1 of 2015 is 0.795.  There will be an output from week 1 of 2016 and an output from week 1 of 2017.  My final output will be the average of week 1 across all three years, the average of week 2 for all three years and so on.   

 

I already have the data in one table and a separate dates table with my company's fiscal year, etc.  

 

Can someone help me create these two measures? 

 

 Capture3.PNG

Labels:
Message 1 of 8
8,307 Views
0
7 REPLIES 7
v-shex-msft
Community Support
Community Support

‎11-12-2017 10:33 PM

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]
)

2.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 2 of 8
8,287 Views
0
jasontlambert
Frequent Visitor
In response to v-shex-msft

‎11-13-2017 01:33 PM

@v-shex-msft, 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.  

 

 Capture4.PNG

Message 3 of 8
8,279 Views
0
Ashish_Mathur
Super User
Super User
In response to jasontlambert

‎11-13-2017 04:29 PM

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 8
8,272 Views
0
jasontlambert
Frequent Visitor
In response to Ashish_Mathur

‎11-14-2017 06:18 AM

@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
Message 5 of 8
8,261 Views
0
Ashish_Mathur
Super User
Super User
In response to jasontlambert

‎11-14-2017 11:14 PM

Hi @jasontlambert,

 

You may refer to my solution this file.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 8
8,253 Views
0
jasontlambert
Frequent Visitor
In response to Ashish_Mathur

‎11-15-2017 07:23 AM

Thank you @Ashish_Mathur! While this works for the single product "A" that I've supplied in the sample data, I'm not able to replicate the same results once I apply this logic to my entire data set.  I have thousands of products that are divided out into different product categories.  When I attempt to add a second product "B", I get this error: 

 

Data
Column 'Date' in Table 'Data' contains a duplicate value '1/3/2015' and 
this is not allowed for columns on the one side of a many-to-one 
relationship or for columns that are used as the primary key of a table.

Once everything is working properly, I ultimately will need to be able to use slicers to pick different individual products, or view total categories of products.  I hope this helps.  

Message 7 of 8
8,248 Views
0
Ashish_Mathur
Super User
Super User
In response to jasontlambert

‎11-15-2017 04:31 PM

Hi,

 

Share such a dataset and show me the expected result on that dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 8 of 8
8,240 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All