Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to write a DAX formula where I am able to take the average basket size for a basket containing item "A".
for each basket containing item "A", sum the sales in each basket and then take the average across all baskets. Baskets containing item "A" can contain other items. These items must be included in the sum of sales.
Currently, the DAX formula below is summing only the item I have selected in a slicer. I need this DAX to sum across all items in transactions containing the item selected in the slicer.
VAR b = SUMMARIZE ( Sales, Sales[TrxID], "basket_sales_sum", SUM ( Sales[Sales] ) ) RETURN AVERAGEX ( b, [basket_sales_sum] )
Solved! Go to Solution.
Hi @Anonymous,
have a look at the following Measure
TRX with A = VAR hasItemTable = CALCULATETABLE('Sales', Sales[Item] = "A") return CALCULATE( divide(sum(Sales[Amount]),COUNTROWS(VALUES('Sales'[TrxId]))), filter('Sales', CONTAINS(hasItemTable,[trxId], Sales[TrxId]) ) )
-> define table with only rows that contain "A" type of Item
-> calculate the basket / the number of trx where the trxId exists in the in the first step
Richard
Proud to be a Super User!
Hi @Anonymous
Could you share some data which could reproduce your scenario and your desired output?
How to Get Your Question Answered Quickly
Regards,
Cherie
Hi,
Share a dataset and show the expected result.
Hi @Anonymous,
have a look at the following Measure
TRX with A = VAR hasItemTable = CALCULATETABLE('Sales', Sales[Item] = "A") return CALCULATE( divide(sum(Sales[Amount]),COUNTROWS(VALUES('Sales'[TrxId]))), filter('Sales', CONTAINS(hasItemTable,[trxId], Sales[TrxId]) ) )
-> define table with only rows that contain "A" type of Item
-> calculate the basket / the number of trx where the trxId exists in the in the first step
Richard
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |