Forum Discussion

SwathiKundar's avatar
SwathiKundar
Frequent Visitor
4 years ago

Average Daily Spend

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

Need Help
Show and Tell

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    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.