How to calculate a value with dynamic count

[Anonymous]

Hi I would like to as the following advice:

	Item	Sales
2019	A	5
2020	A	10
2020	B	20
2021	A	15
2021	B	30

So when i select the timeline 2020, the calculation will give me an average (5+15)/2, when select 2021, the calculation will be (5+10+15)/3 for item A

While the same for item B, when I select 2020, the average will be 20 and for 2021, the calculation will be (20+30)/2

Overall the equation will sum up the total base on time range selected and divide the total base on how many count found within that period and provide me an average for the item.

Thank you.

[Adescrit]

Hi  @Anonymous

Does this work for you:

Average = 
VAR __Year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR __YearCheck = IF( ISBLANK( __Year ), MAX( 'Table'[Year] ), __Year )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        ALLSELECTED ( 'Table'[Item] ),
        'Table'[Year] <= __YearCheck
    )

P.S. if you don't filter on a year it will take an average over all years.

[Anonymous]

Hi, 

Sorry I forgot to mention the year also contains date and the expression is like "01 Jun 2019", is it possible to adjust the formula to suit that?

Thank you.

[Adescrit]

If you replace the Year column with a reference to the Date column in the same formula, does that work?

[Anonymous]

The formula is working but unfortunately the result is not as the same as expected.

The formula averaged both A & B item together and come up with 1 total average instead of 2 separated average for item A & B.

The above result is item A (5+10) and B (20) , the average under this formula become (5+10+20)/3 = 11.67 instead of 10 for item A and 20 for item B.

Furthermore if it is possible can the outcome be summing up the average from both A and B? (in this case my hope is to have the sum of the average (10+20) = 30

Thanks a lot.

[Adescrit]

Hi @Anonymous 

How about this:

Average2 = 
VAR __Date = SELECTEDVALUE( 'Table'[Date])
VAR __Calc = SUMX( VALUES( 'Table'[Item] ), CALCULATE( AVERAGE( 'Table'[Sales] ),  'Table'[Date] <= __Date ) )
RETURN
    __Calc

[Anonymous]

Hi Adescrit,

Thanks for coming back, the formula is not returning any value if I pull and select more than 1 date in the timeline slicer.

But if i select a specific date, it does calculate the average of for all the value before the selected date, any chance we can adjust the date filter?

Thanks.

[Adescrit]

Hi @Anonymous 

Can you please confirm for me how you expect it to work? Should it provide the average for the exact date(s) you have selected?

Based on your examples I assumed it was an average of all dates up to and including the one selected.

Adrian

[Anonymous]

Hi Adrian,

Sorry for the confusion, let me rephase my question below.

Data Example:

Year	Item	Sales
01/01/2019	A	5
01/05/2019	B	20
01/06/2020	A	15
01/06/2020	B	30

I would like to have a caluclation that can provide me a dynamic caluclation on the sum of average.

For example, if I pull the timeline slicer to May 2019 it gives me a sum of average A(5) + average B(25) = 25

And when I adjust the timeline from May 2019 to cover Jun 2020, the result should be total of average A(15) + average B((20+30)/2) with these period which is 40. In this case Jan 2019 data will be excluded.

Appreciate your help!

[Adescrit · Accepted solution]

Thanks for the explanation. In this case I think the answer might be simpler:

Average2 = 
VAR __Calc = SUMX( VALUES( 'Table'[Item] ), CALCULATE( AVERAGE( 'Table'[Sales] )))
RETURN
    __Calc

[Anonymous]

Hi Adrian,

You are amazing, didn't know this formula can be done in this way.

Thanks a lot!