How to get correct averages using SUMMARIZE or GROUPBY

[mweber]

Hello,

I'm relatively new to DAX and am having trouble getting the correct averages from a dataset. Here's a subsection of a datset I'm working with:

Week Ending	Site	SKU Nbr	Units
1/6/2019	Sycamore	1002351566	4680
1/6/2019	Sycamore	1002765363	34260
1/6/2019	Sycamore	1002596200	128
1/6/2019	Sycamore	1003232393	700
1/6/2019	Sycamore	207585	29700
1/6/2019	Sycamore	1001075212	3392
1/6/2019	Sycamore	1002765466	1408
1/6/2019	Sycamore	1002765469	1728
1/6/2019	Sycamore	1001869486	1595
1/6/2019	Sycamore	1003229847	580
1/6/2019	Sycamore	1003229848	144
1/6/2019	Sycamore	1001655065	3072
1/6/2019	Sycamore	1002765353	4048
1/6/2019	Sycamore	1002765358	3300
1/6/2019	Sycamore	1001240215	3328
1/6/2019	Sycamore	1003115503	576
1/6/2019	Kingman	1001869486	935
1/6/2019	Kingman	207585	10116
1/6/2019	Kingman	1001240215	2756
1/6/2019	Kingman	1001075212	1056
1/13/2019	Sycamore	207585	56736
1/13/2019	Sycamore	1001075212	26160
1/13/2019	Sycamore	1002351566	8460
1/13/2019	Sycamore	1002765358	13464
1/13/2019	Sycamore	1002765469	17832
1/13/2019	Sycamore	1001869486	18920
1/13/2019	Sycamore	1002765363	55050
1/13/2019	Sycamore	1001240215	28392
1/13/2019	Sycamore	1003232393	904
1/13/2019	Sycamore	1003229847	488
1/13/2019	Sycamore	1002765466	656
1/13/2019	Sycamore	1001655065	2496
1/13/2019	Sycamore	1002765353	880
1/13/2019	Sycamore	1003115503	1392
1/13/2019	Sycamore	1003229848	180
1/13/2019	Sycamore	1002596200	128
1/13/2019	Kingman	1001240215	15184
1/13/2019	Kingman	1001075212	12880
1/13/2019	Kingman	1001869486	3960
1/13/2019	Kingman	207585	4356
1/20/2019	Sycamore	1002351566	10820
1/20/2019	Sycamore	1001075212	27680
1/20/2019	Sycamore	1002765469	13736
1/20/2019	Sycamore	207585	55548
1/20/2019	Sycamore	1002765358	13728
1/20/2019	Sycamore	1003115503	2592
1/20/2019	Sycamore	1003232393	228
1/20/2019	Sycamore	1002765353	2552
1/20/2019	Sycamore	1003229847	76
1/20/2019	Sycamore	1001869486	14410
1/20/2019	Sycamore	1002765363	25800
1/20/2019	Sycamore	1003229848	18
1/20/2019	Sycamore	1002765466	1200
1/20/2019	Sycamore	1001240215	25896
1/20/2019	Sycamore	1001655065	4064
1/20/2019	Kingman	1001075212	20928
1/20/2019	Kingman	1001869486	9625
1/20/2019	Kingman	1001240215	22620
1/20/2019	Kingman	207585	49896

What I'm trying to accomplish is to simply get the Average Weekly Units for each SKU Nbr. I've tried quite a few methods... measures, columns... average x, Summarize, Group by, etc... I  can't get the correct averages for each SKU. In excel, all you have to do is create a pivot on week ending, sum up the SKU, and see what the average is across all weeks. (e.g. the answer for 207585 is 68,784/week) I can't seem to replicate this using DAX. Any help would be greatly appreciated. 

[AlB]

Hi @mweber 

Try this:

1. Place Table1[SKU Nbr] in the rows of a matrix visual

2. Create this measure and place it in the visual:

Measure =
DIVIDE ( SUM ( Table1[Units] ); DISTINCTCOUNT ( Table1[Week Ending] ) )

[MFelix · Accepted solution]

Hi @mweber ,

Try this measure:

Average per week = SUM('Table'[Units]) / CALCULATE(DISTINCTCOUNT('Table'[Week Ending]))

Regards,

MFelix

[mweber]

MFelix,

That worked! I've literally been beating my head against the wall for a few days on that one 🙂 I knew it was probably an easy solution but am just starting to get comortable with how calculate works.  Another question but on the same dataset:

How then to get the standard deviation of that result. Said another way, what is the Standard Deviation of the SKU across weeks.

MWeber