Reporting Average Monthly Spend to obtain Monthly Runrate

[WIREDOG]

I have a Table that shows itemized Store expenses by date.  I have added a Measured Column that shows a Three-Month Average Run rate.  (I also have a necessary dim.date table to allow me date calculations.)  The run rate works well at the most granular level--

• Store
• Account Numbers (not Displayed)
• Date
• Spend ($)
• MEASURED COLUM: Three-Month Average Run Rate

 However, I would like to be able to see the Three-Month Average Run rate at the store level without the [Account Numbers], so a Run Rate at a store level as a whole.  So--

• Store
• Month
• Total Monthly Spend
• Three-Month Average Run Rate

The problem I am running into is that the Monthly Run rate is off when there are more that one [Spend] value in a month.  It appears to be averaging the total number of [Spend] values as opposed to Totaling the [Spend] in a month FIRST, then giving an average over the number of Months.

Is there a function that allows me to do see the MONTHLY Average Run rate while still retaining the ability to get to the granular level data if I need to investigate?

Thank you,

Ben

[v-yulgu-msft]

Hi @WIREDOG,

Please share sample data of original data table, instead of visual data. Also, please illustrate your desired result with examples.

Regards,

Yuliana Gu