Calculate Average Expense

[Anonymous]

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!

[v-shex-msft · Accepted solution]

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

[amitchandak]

@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())