cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mtt_90
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())
return
CALCULATE(
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!
5 REPLIES 5
Mtt_90
Frequent Visitor

Link to the sample data

https://drive.google.com/file/d/1bu0xuA4qcpLq3HkJ6MU79k6RAnU8eO7V/view?usp=sharing

littlemojopuppy
Super User I
Super User I

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

Mtt_90_1-1611095159165.png

 

Hi, thank you for your reaply.

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

Thanks! 

 

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

https://drive.google.com/file/d/1bu0xuA4qcpLq3HkJ6MU79k6RAnU8eO7V/view?usp=sharing

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

Top Solution Authors
Top Kudoed Authors