Forum Discussion
Average Daily Spend by Month
Hi sprather,
>>So for example today is day 22 of May. This report should show the owners if they pull up the portal for spending what our average daily spend is for May. So if we have spent 25000 through the 21st
You can create a measure to get your result.Average_Current=CALCULATE(SUM(Table[price]),FILTER(Table, Table[Month]=MAX(Table[Month])))/CALCULATE(COUNT(Table[purchase_date]),FILTER(Table, Table[Month]=MAX(Table[Month])&&FILTER(Table, Table[purchase_date]<MAX(Table[purchase_date])))
>>Ideally be able to pull up historical data as well so they can compare over time the average daily spending by month.
Please try to create the following formula, please test it using your sample data.average = Var currentmonth=MAX(Table[Month]) RETURN IF( FIRSTNONBLANK(MAX(Table[Month]),1)=currentmonth, Table[Average_Current], CALCULATE(AVERAGE(Table[price]),FILTER(Table,Table[Month])) )
Best Regards,
Angelia
I would start with just the default Average aggregation for Total Price. For month to date, I would use TOTALMTD function.
https://msdn.microsoft.com/en-us/library/ee634560.aspx
Thanks! I will try that. There are so many functions I am mainly looking for a palce to start. I appreciate you giving me one. I will let you know if that is all I needed!
Hi sprather,
You want to create the average for each month. You can use the formula to create a calculated column.Average=CALCULATE(Average(Table[value]),ALLEXCEPT(Table,Table[Month]))
If this is not what you want, you'd better share your sample data or .pbix file for further analysis. And your requirement is still confusing, please describe it clearly.
Best Regards,
AngeliaI will play with this function you provided but let me give more precise details.
This is for our PO system in QuickBase and we have a target of $3200/day of spending for our yards. When one of the owners pulls up the PowerBI app they will want to see month-to-date average $ spent/day to see if we are on pace. So for example today is day 22 of May. This report should show the owners if they pull up the portal for spending what our average daily spend is for May. Ideally be able to pull up historical data as well so they can compare over time the average daily spending by month.
For Current Month the value should be the total $ spent through the day prior to the current day divided by the # of days in the month - today. So if we have spent 25000 through the 21st (we do not want to include the current day in the calculation as this days spending has not been completed).
For previous months the calculation should be total $ spent for the month divided by the number of days in the month.
I have already configured a interface to pull all the data from QuickBase using the QuNext ODBC driver/on-premise gateway with scheduled data pulls. So I have all the data flowing....Now I just have to understand the syntax requirements for manipulating the data effectively in PowerBI.
Hope this makes sense....
Hi sprather,
>>So for example today is day 22 of May. This report should show the owners if they pull up the portal for spending what our average daily spend is for May. So if we have spent 25000 through the 21st
You can create a measure to get your result.Average_Current=CALCULATE(SUM(Table[price]),FILTER(Table, Table[Month]=MAX(Table[Month])))/CALCULATE(COUNT(Table[purchase_date]),FILTER(Table, Table[Month]=MAX(Table[Month])&&FILTER(Table, Table[purchase_date]<MAX(Table[purchase_date])))
>>Ideally be able to pull up historical data as well so they can compare over time the average daily spending by month.
Please try to create the following formula, please test it using your sample data.average = Var currentmonth=MAX(Table[Month]) RETURN IF( FIRSTNONBLANK(MAX(Table[Month]),1)=currentmonth, Table[Average_Current], CALCULATE(AVERAGE(Table[price]),FILTER(Table,Table[Month])) )
Best Regards,
Angelia