Forum Discussion
Helper ICalculate annual average
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
13 Replies
Pldoyon1So, 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.
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
Community Champion
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.
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
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]))RETURNDIVIDE ( SUM ( 'Table'[Cost] ), DATEDIFF ( _mindate, _maxdate, YEAR ) + 1 )Any comment or problem, please feel free to let me know.
Best Regards
