topic Re: Count Distinct HELP!!! in App Development

Count Distinct HELP!!!

macuevah — Wed, 03 Jan 2018 23:15:36 GMT

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?

Re: Count Distinct HELP!!!

sunny_talwar — Thu, 04 Jan 2018 00:08:41 GMT

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

Re: Count Distinct HELP!!!

macuevah — Thu, 04 Jan 2018 00:12:58 GMT

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

Re: Count Distinct HELP!!!

sunny_talwar — Thu, 04 Jan 2018 00:41:56 GMT

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?

Re: Count Distinct HELP!!!

pradosh_thakur — Thu, 04 Jan 2018 04:23:00 GMT

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

Re: Count Distinct HELP!!!

kaanerisen — Thu, 04 Jan 2018 04:57:59 GMT

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.