Average Daily Spend

[SwathiKundar]

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

[V-lianl-msft]

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.

[AlexisOlson]

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

[SwathiKundar]

If it's possible then how achieve it.

[AlexisOlson]

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.