Calculate annual average

[Pldoyon1]

Hello guys, hope you're doing well ! I want to calculate the annual average so I try to simply divide  DIVIDE([Cost],DISTINCTCOUNT([Year]))  but in my dataset, I don't have the 0 value for each year for each dimension and I want also to filter the year with a slicer. So, I can't do this : DIVIDE([Cost],CALCULATE(DISTINCTCOUNT([Year]),ALL()) or DIVIDE([Cost],CALCULATE(DISTINCTCOUNT([Year]),ALLEXCEPT(Table, [Year]))

So there're some options that I'm thinking of :

- add all the rows with 0 that doesn't exist in my database but it's too many

- create a new table with only YEAR but because there's no relation between these 2 tables, I can't use the year slicer

- use many to many relation (CROSSFILTERED)

- what if parameter

Can someone help me please ?

I need to find a way to calculate the annual average with all the years that the user will choose in the slicer, not only the year of the dimension because there's no 0 for all year. Hope that's understandable.

Thanks a lot

[v-yiruan-msft]

Hi @Pldoyon1 ,

I'm not very clear about your requirement. Could you please explain more and provide your expected result with example? For example the scenario in your last post, what's the final expected result? I updated my sample pbix file, please check whether that is what you want.

Best Regards

[Pldoyon1]

Hello Yinggyinr, thanks a lot for your reply and effort to help me. The result that I'm looking for in your example is this :

With the data of the category A (but the lines of 0 value doesn't exist)

2017 : 0

2018 : 700

2019 : 0

2020 : 0 

The annual average is : 700 / 4 = 175

So because there's no data in your model for the category A for all years, it can't calculate correctly. Powr Bi should provide a way to consider the numbers of years selected by the user in the slicer. Now, we can't filter a category and get the right average corresponding with the date selected in the slicer.

Hope that is more clear.

Thanks

[v-yiruan-msft]

Hi @Pldoyon1 ，

I updated the formula of measure as below, please check whether the result is correct or not:

Measure =

VAR _mindate =

CALCULATE( MIN ( 'Table'[Date] ),REMOVEFILTERS('Table'[Category]))

VAR _maxdate =

CALCULATE( MAX( 'Table'[Date] ),REMOVEFILTERS('Table'[Category]))

RETURN

DIVIDE ( SUM ( 'Table'[Cost] ), DATEDIFF ( _mindate, _maxdate, YEAR ) + 1 )

Any comment or problem, please feel free to let me know.

Best Regards

[Pldoyon1]

Hello @v-yiruan-msft , thanks for your update. It's really appreciated. Unfortunatley, when I open your file, the measure is not 175 but still 725. So, I don't know if you configure something else with your Power Bi but mine (last version in fev 2021) gives me 725 ! The good answer is 175. I tried the same with ALL() or ALLEXCEPT() as I said in my first post and it doesn't work. If you calculate and use REMOVEFILTER(Category), the logic is that it won't consider the filter Category and this is what Power Bi is doing.

If you have other idea, it will be welcomed.

Best regards

[v-yiruan-msft]

Hi @Pldoyon1 ,

Actually, I didn't make any other configure for these visuals. You can check the attachment for the details.

Best Regards

[Pldoyon1]

Hello Yingyinr, I still have the same result of 725 and not 175 with your file. I don't know how you get this result.

[v-yiruan-msft]

Hi @Pldoyon1 ,

I updated my sample pbix file with the following steps:

1. Create a date dimension (don't create the relationship with the fact table)

2. Update the formula of measure as below:

Measure =

VAR _mindate =

CALCULATE( MIN ( 'Date'[Date] ))

VAR _maxdate =

CALCULATE( MAX( 'Date'[Date] ))

RETURN

DIVIDE ( SUM ( 'Table'[Cost] ), DATEDIFF ( _mindate, _maxdate, YEAR ) + 1 )

Best Regards

[Pldoyon1]

Hello @v-yiruan-msft , thanks for your effort. I think you are in the right path. The only solution I see is passing by a disconnected date table. But, it still doesn't work because if you filter the slicer of date, it won't filter the other table with the cost. So, the sum of cost won't be good with the dates selected in the slicer. There's certainly a way to filter the table of cost with a dax measure with the dates selected in the date table.

[Pldoyon1]

So, I solve my problem creating a table with the number of years between  a couple of period of times that the user can select instead to be able to personnalize the period with a normal slicer. If someone know how to do it with a normal slicer, it will be appreciated.

[v-yiruan-msft]

Hi @Pldoyon1 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

If the above one is not applicable for your scenario, please provide some sample data(exclude sensitive data) and your expected result with example or screenshot. Thank you.

Best Regards

[Pldoyon1]

Hello Yingyinr, thanks a lot for your help. Unfortunately, it doesn't work. If you add another column in your table of categories and there's only one category (lets say A) in 2018 and you filter the date between 2017 and 2020, and filter the category A, the min and max date will be only one row of category A in 2018. So, the average will consider only the min and max date of this row. The average has to consider non existing data (data with 0 removed) between the dates in the slicer. 

[AllisonKennedy]

@Pldoyon1 This sounds like you need a DimDate table (or at very least DimYear), so yes, create a new table with YEAR and relate that to fact table. Preferrable it's a DimDate table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Use the AVERAGEX( VALUES(DimDate[Year]), [Cost]) to calculate the average.

https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html 

[Pldoyon1]

Hello Allison, thanks for your response but it doesn't work. I add the DimDate as you said with a link with my fact table and that does not consider the non existing 0 value in the average calculation when I filter a dimension.