Forum Discussion
Calculate Average Expense
Hello,
I've been trying for the past hour, alongside searching on here, to calculate average daily spend by category and failing miserably... I wondered if anyone could provide advice. In summary, I have:
Column A = Names
Column B = Type of Expense (say 'Flights', 'Petrol' et cetera)
Column C = Expense Quantity
Calculated value = Number of days charged
I have already got measures for total spend (e.g. total flight spend by person), but I am now trying to calculate the average for each type of expense by the name; for example in Excel I could do...:
=IF(AND(ColumnA="John Smith",ColumnB="Petrol"),ColumnB/Calculated Value,"Error")
I would be looking to replicate this for each type of expense; note that the calculated value for days charged is obviously going to be the same for the specific name. As an example:
| Name | Type of Expense | Calculated Value (Days Charged) | Total Expenses for Type of Expense | Average |
| John Smith | Flight | 5 | 100 | 60 |
| John Smith | Flight | 5 | 200 | 60 |
| John Smith | Petrol | 5 | 50 | 10 |
| Joe Bloggs | Flight | 20 | 500 | 25 |
Thanks for any help that you can provide!
HI Anonymous,
I think you may need to write a variable in your formula with summarize function to add custom filed apply the first aggregate calculation, then you can use iterator function on these values to apply second aggregate mode.
For example:
Measure = VAR summary = SUMMARIZE ( ALLSELECTED ( Table ), [Name], [Type of Expense], "Total", [TotalExpense] ) RETURN AVERAGEX ( summary, [Total] )You can take a look at the following blog about measure calculation on the total level:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
2 Replies
Anonymous , columns not seem correct to me
But you can have measure like this
=IF(AND(table[Name]="John Smith",Table[Type of Expense]="Petrol"),divide(count(Table[Type of Expense])/sum(Table[ Calculated Value (Days Charged)])),blank())
HI Anonymous,
I think you may need to write a variable in your formula with summarize function to add custom filed apply the first aggregate calculation, then you can use iterator function on these values to apply second aggregate mode.
For example:
Measure = VAR summary = SUMMARIZE ( ALLSELECTED ( Table ), [Name], [Type of Expense], "Total", [TotalExpense] ) RETURN AVERAGEX ( summary, [Total] )You can take a look at the following blog about measure calculation on the total level:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
