## Avg invoice amount

Anything related to PowerPivot and DAX Formuale
zzzzz
Posts: 5
Joined: Fri Jun 24, 2016 3:09 pm

### Avg invoice amount

hi all

i have the below power piviot table (data model)
customer # salesman invoice# item # qty price amount
a1 jack 1 11 2 12.00 24.00
a1 jack 1 12 3 10.00 30.00
b1 john 2 11 1 12.00 12.00
b1 john 2 12 3 5.00 15.00
b1 john 2 13 5 6.00 30.00

i would like to get a measure that would give me average invoice amount

what it would have to do is total up the amount column for each invoice and then give me an average of that total

how can i accomplish that? any help is appreciated

thanks

MattAllington
Posts: 922
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Avg invoice amount

Total =sum(table[qty])
avg = average[table[qty])
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

### Re: Avg invoice amount

Hey Matt,

I think that gives the wrong average. As you have multiple rows for an invoice, you need to total the invoice first, then get the average of the invoices then.

Cheers
Phil

MattAllington
Posts: 922
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Avg invoice amount

You're right Phil. I didn't notice the repeating invoice numbers
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Oxenskiold
Posts: 30
Joined: Tue Jan 05, 2016 10:38 pm

### Re: Avg invoice amount

Hi zzzzz,

AVERAGEX ( VALUES ( table[invoice#] ), CALCULATE ( SUM ( table[Amount] ) ) ) ??