topic Re: Average Daily Spend in Developer

Average Daily Spend

SwathiKundar — Mon, 20 Dec 2021 19:01:26 GMT

Hello All.

I need to calculate the Average Daily Spend.

Average Daily Spend = Total cost / Avg Days for the selected period.
ex: If we select the period 1 to 25 then Average days should be 25.

currently total rows of Average Daily Spend column in average. But I need to show it in SUM instead of AVERAGE.
According to the below DAX, logic is working fine except for total rows.

Avg Days =

var startofthisperiod = FIRSTDATE(Dates[Date])

var endofthisperiod =LASTDATE(Dates[Date])+1

var noOfDays = DATEDIFF (startofthisperiod, endofthisperiod,DAY)

return

noOfDays

AVG Daily Spend =

DIVIDE(SUM(Table[Cost]),

[Avg Days]

)

Thanks in Advance.
Swathi Kundar

Re: Average Daily Spend

AlexisOlson — Tue, 30 Nov 2021 19:08:55 GMT

It's possible but that's a pretty strange total to show. How do you interpret it? What does that sum of averages represent?

Re: Average Daily Spend

SwathiKundar — Wed, 01 Dec 2021 18:43:38 GMT

If it's possible then how achieve it.

Re: Average Daily Spend

AlexisOlson — Wed, 01 Dec 2021 19:30:37 GMT

You'd need to iterate over one level of granularity where you take the average below that level and sum up those averages. I can't tell what determines what level of granularity you want to change your aggregation rule at though.

Re: Average Daily Spend

V-lianl-msft — Tue, 07 Dec 2021 06:04:47 GMT

Hi @SwathiKundar ,

Please try to create a measure like below:

Measure = SUMX(VALUES('Table'[Month]),[AVG Daily Spend])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.