Forum Discussion
Average Weekly Sales
GOT IT!!!
AWUS Actual =
VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
)
)
Hi DaveyP, may I ask what you expect the answer to be in your dataset that you provided?
Hi Dozer
Sorry to confuse, below is the example in more detail, the results i keep getting are essentially all stores sales added together (9300) divided by count of stores (2325) divided by count of weeks (581.25)
| Week | Store1 | Store2 | Store3 | Store4 | AWUS | |
| 1 | 400 | 500 | 700 | 600 | 550 | (All stores sales divide by count of store (4)) |
| 2 | 500 | 600 | 800 | 700 | 650 | (All stores sales divide by count of store (4)) |
| 3 | 600 | 700 | 900 | 800 | 750 | (All stores sales divide by count of store (4)) |
| 4 | 700 | 800 | 750 | (All stores sales divide by count of store (2)) | ||
| 675 | Average sales = all weeks AWUS / Count of weeks |
Well this is interesting...adding an Average Line, You get the $675 but I do not know how it's calculated.
GOT IT!!!
AWUS Actual =
VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
)
)
How about this solution?
1) create a measure
AWUS = AVERAGEX(VALUES(Table1[Week]),CALCULATE(AVERAGE(Table1[Sales])))
2) use matrix, set AWUS as Values.