ave invoice lines

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

ave invoice lines

Postby zzzzz » Tue May 30, 2017 4:04 am

hi

i have my transaction detail table loaded to power pivot.

customer number,customer name ,invoice number,item # ,qty,price ,amount etc

what i would like is a measure that tells me the average invoice lines

the measure would have to do two things, it has to add up the total lines per invoice, there is obviously multiple line items for each invoice
and then give me a average invoice lines

so back in my pivot table i can see average lines per invoice by salesman or by customer etc

P.S. I do have an index column in my data model but it’s not per invoice it’s for the whole table

any help is greatly appreciated

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

Re: ave invoice lines

Postby MattAllington » Tue May 30, 2017 6:12 am

Depending on the size of your table, you could consider denormalising the customer name and number into a lookup table. This should save a lot of space. Also depending on what etc means, you may not need all those extra columns - also saving a lot of space

Based on what you have said, i would simply write these measures (ironically these are directly from my training course I taught yeasterday)

Total invoices = distinctcount(table[invoice number])
Total items = sum(table[qty])
Avg items per invoice = divide([total items],[total invoices])
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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests