topic Re: Average Basket for a list of items in DAX Commands and Tips

Average Basket for a list of items

Mtt_90 — Fri, 15 Jan 2021 21:55:16 GMT

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!

Re: Average Basket for a list of items

littlemojopuppy — Sat, 16 Jan 2021 17:20:39 GMT

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

Re: Average Basket for a list of items

Mtt_90 — Tue, 19 Jan 2021 22:29:55 GMT

Hi, thank you for your reaply.

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

Thanks!

Re: Average Basket for a list of items

littlemojopuppy — Wed, 20 Jan 2021 00:09:46 GMT

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

Re: Average Basket for a list of items

Mtt_90 — Wed, 20 Jan 2021 14:23:14 GMT

Sorry, here's the link

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

Re: Average Basket for a list of items

Mtt_90 — Wed, 20 Jan 2021 14:23:33 GMT

Link to the sample data

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