Forum Discussion

Pldoyon1's avatar
Pldoyon1
Icon for Helper I rankHelper I
5 years ago

Calculate 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 ...
Need Help
Pldoyon1's avatar
Pldoyon1
Icon for Helper I rankHelper I
5 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Pldoyon1
      Icon for Helper I rankHelper I
      5 years ago

      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.