topic Re: How to calculate a value with dynamic count in DAX Commands and Tips

How to calculate a value with dynamic count

Anonymous — Thu, 01 Sep 2022 04:34:33 GMT

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.

Re: How to calculate a value with dynamic count

Adescrit — Thu, 01 Sep 2022 08:11:13 GMT

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.

Re: How to calculate a value with dynamic count

Anonymous — Thu, 01 Sep 2022 10:32:28 GMT

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.

Re: How to calculate a value with dynamic count

Adescrit — Thu, 01 Sep 2022 11:57:14 GMT

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

Re: How to calculate a value with dynamic count

Anonymous — Thu, 01 Sep 2022 13:10:59 GMT

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.

Re: How to calculate a value with dynamic count

Adescrit — Fri, 02 Sep 2022 08:28:13 GMT

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

Re: How to calculate a value with dynamic count

Anonymous — Fri, 02 Sep 2022 10:28:10 GMT

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.

Re: How to calculate a value with dynamic count

Adescrit — Fri, 02 Sep 2022 10:31:30 GMT

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

Re: How to calculate a value with dynamic count

Anonymous — Fri, 02 Sep 2022 10:50:31 GMT

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!

Re: How to calculate a value with dynamic count

Adescrit — Fri, 02 Sep 2022 12:00:58 GMT

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

Re: How to calculate a value with dynamic count

Anonymous — Fri, 02 Sep 2022 13:34:17 GMT

Hi Adrian,

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

Thanks a lot!