cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Average monthly value per invoice with multiple line items per invoice

Hi all,

I have the following table:

InvoiceDate     InvoiceNumber     InvoiceLineItem     InvoiceLineItemValue     InvoiceTotalValue

01/01/2019     1                            A                            £20                                 £100

01/01/2019     1                            B                            £30                                 £100

01/01/2019     1                            C                            £50                                 £100

02/01/2019     2                            D                            £40                                 £40

03/10/2019     3                            E                            £60                                  £200

03/01/2019     3                            A                            £20                                 £200

03/01/2019     3                            F                            £120                                £200

etc...

I would like to create a simple Clustered Column Chart, where I can show Average Invoice Value by InvoiceDate (Year, Quarter, Month, Day).

Thanks!

2 REPLIES 2
Super User

@chriser best practice is to add a calendar/date dimension in your model, there are many post on how to add one either using DAX or Power Query.

- set relationship on date from date diemsnion with your invoice table

- add new measure called AVERAGE( Invoice[InvoiceTotalValue])

- add bar chart, put Year-> Quarter ->Month->Date on x-axis and average measure on value

now you can drill up and down to each level and see the average by invoice.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Thanks @parry2k, if I understand your answer, the problem with AVERAGE formula is that it will average all InvoiceTotalValues but it will count all of them per each InvoiceLineItem.

In my example if I have multiple invoices per day with different InvoiceTotalValue, number of InvoiceLineItems will make a difference to the average, which will give me a wrong average.

For example:

InvoiceDate     InvoiceNumber     InvoiceLineItem     InvoiceLineItemValue     InvoiceTotalValue

01/01/2019     1                            A                            £20                                 £100

01/01/2019     1                            B                            £30                                 £100

01/01/2019     1                            C                            £50                                 £100

01/01/2019     1                            A                            £40                                 £200

01/01/2019     1                            B                            £60                                 £200

01/01/2019     1                            C                            £100                               £200

Average is 150

but for:

InvoiceDate     InvoiceNumber     InvoiceLineItem     InvoiceLineItemValue     InvoiceTotalValue

01/01/2019     1                            A                            £20                                 £100

01/01/2019     1                            B                            £30                                 £100

01/01/2019     1                            C                            £50                                 £100

01/01/2019     1                            A                            £40                                 £200

01/01/2019     1                            B                            £60                                 £200

01/01/2019     1                            D                            £80                                £200

01/01/2019     1                            E                            £20                                 £200

Average is: 157.14

So how do I get a real InvoiceTotalValue Average independant from how many InvoiceLineItems are per Invoice?

Thanks

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.