- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Average the sum of sales for each transaction where transaction contains item "X"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-16-2018 10:33 AM

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.

Accepted Solutions

## Re: Average the sum of sales for each transaction where transaction contains item "X"

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-16-2018 11:40 AM - edited 11-16-2018 11:40 AM

Hi @tsailfc,

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

All Replies

## Re: Average the sum of sales for each transaction where transaction contains item "X"

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-16-2018 11:40 AM - edited 11-16-2018 11:40 AM

Hi @tsailfc,

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

## Re: Average the sum of sales for each transaction where transaction contains item "X"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-17-2018 01:14 AM

Hi,

Share a dataset and show the expected result.

## Re: Average the sum of sales for each transaction where transaction contains item "X"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-18-2018 09:50 PM

Hi @tsailfc

Could you share some data which could reproduce your scenario and your desired output?

How to Get Your Question Answered Quickly

Regards,

Cherie

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*