cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tsailfc Frequent Visitor
Frequent Visitor

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

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] )

 

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

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

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

3 REPLIES 3
richbenmintz
Advisor

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

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

Super User
Super User

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

Hi,

 

Share a dataset and show the expected result.

Community Support Team
Community Support Team

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.