cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to calculate a value with dynamic count

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.

1 ACCEPTED SOLUTION
Impactful Individual

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``````

10 REPLIES 10
Impactful Individual

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
Not applicable

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.

Impactful Individual

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

Anonymous
Not applicable

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.

Impactful Individual

Hi @Anonymous

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

Anonymous
Not applicable

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.

Impactful Individual

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.

Anonymous
Not applicable

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.

Impactful Individual

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
Not applicable

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

Thanks a lot!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.