Avg invoice amount

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

Avg invoice amount

Postby zzzzz » Fri May 19, 2017 8:28 am

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: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Avg invoice amount

Postby MattAllington » Sun May 21, 2017 4:01 pm

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

Postby PhilC » Mon May 22, 2017 9:10 am

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: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Avg invoice amount

Postby MattAllington » Mon May 22, 2017 8:11 pm

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: 29
Joined: Tue Jan 05, 2016 10:38 pm

Re: Avg invoice amount

Postby Oxenskiold » Mon May 22, 2017 9:25 pm

Hi zzzzz,

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest