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!
