Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @mweber ,
Try this measure:
Average per week = SUM('Table'[Units]) / CALCULATE(DISTINCTCOUNT('Table'[Week Ending]))
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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] ) )
Hi @mweber ,
Try this measure:
Average per week = SUM('Table'[Units]) / CALCULATE(DISTINCTCOUNT('Table'[Week Ending]))
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |