Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!!
I'm trying to use the following expression to express the sales avg considering the number of months.
Example:
Sum Sales in last 11 years = 654,229,440
Months for the period of time selected = ex: 11 years = 132 months (I have an expresión called Year-Month for every sales month "2017-01-Jan","2017-02-Feb" etc)
My expression is: =Sum(Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))
If there's no selection the expression works
654,229,440 / 132 = average sales per month
But when I select one specific year or more it still divides sales by 132 months, considering that if I select 1 year the formula should be:
654,229,440 / 12 = average sales per month
Or if I select 2 years:
654,229,440 / 24 = average sales per month
And so on, what am I doing wrong?
Are you sure the denominator is not changing? I think it seems that you don't want the numerator to change with selection? May be this
=Sum({$<[Year]>} Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>} [Year-Month]))
I Think it's easier to look at if I only focus en the second part of the expression:
=(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))
I have 11 years info, so no filters that is equal to 132, but if I select 2017 that should be equal to 12, but It's still 132
Unless your data model is not well connected, I don't see why Count of YearMonth field won't drill down on selection of Year... would you be able to share a sample where you can show the issue?
does it change when you not use single quotes in '2006' and just 2006. The expression should work.
The only possible explanation i can think of which may forced you to think its not working is if all the months have same number of sales and the average will always be same. and you are assuming that the numerator will not change so that may be the case. but that is highly unlikely
Hi,
If i understand your need correctly, you can use aggr for this.
Avg(Aggr(Sum(Subt),[Year-Month]))
This will show you the average monthly sales based on your selections.