Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Average Basket for a list of items

Hi everyone,

I'm new to Power BI and I'm stuck (actually I'm lost) in an operation. 


I have one table:

1) Sales table that has the following columns: Invoice ID (it records every single transaction, so if someone buys 3 products, there will be three rows and each one will have Invoice-001 as Invoice ID since they are part of the same transaction), quantity, date, $ sales, promotion name column (it will have the name of the promotion if the product is part of it) and product name.


I would like to know what is the average basket sales $ when a product is in the basket and what is the average basket sale when there isn't any promoted product in the basket.


Moreover, multiple items can be part of the same promotion. This mean that if I'm using a slicer for products, I should see the average basket for that product, but If I use the "promotion name" as slicer, I should see the average basket $ for that promotion (which can include multiple products)


So far, I used this DAX, and it partially works because it gives me the average I need, but when I select a product or a promotion with a slicer, the value will change and will consider only the average for that product ( keeping out all the other products that are in the basket)


IN =
var hasItemTable = CALCULATETABLE(Sales,Sales[Promotions - Location.Promotion Name] <> BLANK())
DIVIDE(sum(Sales[TotalSales]), COUNTROWS(VALUES('Sales'[InvoicePrintableId]))),
FILTER('Sales', CONTAINS(hasItemTable,Sales[InvoicePrintableId],Sales[InvoicePrintableId])))
I don't know if it is a matter of filter, slicer or dax formula.
Please, let me know if you need other information!
Thank you so much for the help!
Frequent Visitor

Link to the sample data

Super User I
Super User I

Hi @Mtt_90 can you provide some sample data to work with?



Hi, thank you for your reaply.

This is a screenshot of data. Let me know if you need anything else



I can't work with the data from a picture.  Please post some sample data into Google Drive, Dropbox or OneDrive .

Sorry, here's the link

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.


Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors