Forum Discussion
Post ProdigyHelp with daily average
Hello everyone!
I have the following two formulas:
Net USD average per Day =
1. AVERAGEX(
KEEPFILTERS(VALUES('Date'[Date].[Day])),
CALCULATE(SUM('Revenues DB'[Net USD]))
)
2.
Daily AVG = [Sum Net USD]/[Distinct]
Sum Net USD = SUM('Revenues DB'[Net USD])
Distinct = DISTINCTCOUNT('Date'[Date])
My problem is this
At the daily the average is correct in both formulas
At the quarterly and annual monthly level The average is incorrect
Formula number 1. Brings me a Sum of all the months and all the quarters so the average is high
Formula number 2. brings me only the days were i have a number in that quarter or that month
The correct average for me is
A sum divided by the number of days elapsed up to the same date
Thanks for the helpers
This should get you started:
Net USD average per Day = AVERAGEX( KEEPFILTERS(VALUES('Date'[Date])), CALCULATE(SUM('financials'[ Sales]),financials[ Sales]>0) )I don't fully understand your logic, so you may need to apply the filter differently/earlier.
9 Replies
- Ashish_Mathur
Super User
Hi,
I think in approach 2, your denominator should be days on which a sale was registered. So the measure for Days of sales should be
Days of sales = distinctcount(data[Transaction date])
Daily AVG = [Sum Net USD]/[Days of sales]
Hope this helps.
- lbendlin
That first formula is rather adventurous.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
netanellbendlin Hey!
Attaches a link to the Pbix File:
https://1drv.ms/u/s!AonyYI-TdspHgWQReR5uqvnKLTCF
Another thing, I try that the formula does not take amounts below 0
Thanks
- lbendlin
Thank you for providing the pbix. I noticed that your fact granularity is only on month level, not day level.
That would mean you would first have to decide how "daily average" should actually be defined. By number of days in the month? By number of working days in the month? Excluding holidays? etc.
