Forum Discussion

netanel's avatar
netanel
Post Prodigy
4 years ago
Solved

Daily AVG

Hi All!

 

I have this maesure:

Net USD AVG =
CALCULATE(
DIVIDE( SUM( 'DB 2022'[Net USD] ), COUNTROWS( 'Date' ) ),
keepfilters( 'Date'[Date] < TODAY())
)

It works great
It divides the Data on the current day of the month for example the 16th of January so it will only divide by 16 days
My problem starts that sometimes the Data is only up to 12 or less and then the Divide is incorrect

How do I change the formula so that it only divides the number of days in the existing Data?

 

Thanks 

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    Hi netanel.  This should calculate a daily average for all dates in the filter context

    Daily Average =
AVERAGEX(
	VALUES([Your date field]),
	SUM('DB 2022'[Net USD])
)

     

    Hope this helps!

  • netanel's avatar
    netanel
    4 years ago

    Hi littlemojopuppy  thanks for the response!

     

    Problem number 1:

     

     

     

     

     

     

     

     

    Problem number 2:

    I can not share my data but
    Problem number 2 is simple and I overcame it in my formula above
    The problem is that if every day I get 5000
    So when I go up to the monthly level instead of getting an average of 5000 for January I get 31 times 5000 that it comes out to 155,000
    And if I go up to the quarter level then I get 155K double 3

8 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    Hi netanel.  This should calculate a daily average for all dates in the filter context

    Daily Average =
AVERAGEX(
	VALUES([Your date field]),
	SUM('DB 2022'[Net USD])
)

     

    Hope this helps!

    • netanel's avatar
      netanel
      Post Prodigy
      4 years ago

      littlemojopuppy 

      Hi thanks for the response,


      There are two problems
      1. He also brings data from the 31st day of the month even though I am on the 17th of the month

      2. As soon as I go up in a hierarchy to the level of a month it is summed up all the days of the month and then
      If I earned 5000 a day then the average monthly level becomes 30 times 5000
      And the average should remain 5000
      That is, when I go up to the monthly and quarterly level he sums up all the amounts

       

      thanks

    • netanel's avatar
      netanel
      Post Prodigy
      4 years ago

      littlemojopuppy 

      Who defined this as a solution?
      Nothing was resolved
      And I did not define it as a solution ...

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        4 years ago

        netanel you said that you overcame your issue with the solution in the pic you supplied, which was exactly what I suggested.  The other issue you mentioned is from information not included in the initial request...