Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors