cancel
Showing results for
Did you mean:
Frequent Visitor

## Average Products per Transaction

All,

The end formula I’m trying to achieve is calculating the average # of distinct product line per transaction. So, if I look at one transaction #1616327 they ordered 19 items that were under 4 distinct product lines with an average Gross Revenue of \$26,010.00.

Another example is if I want to take a look at average distinct Items ordered per transaction and average Gross Revenue the below should appear.

The Columns that I have to work with are;

[TransactionID] or [invoiceID]

[Pline]

[Fiscal Date]

[FiscalYear]

[FiscalMonth]

Again, just looking to calculate the average revenue and the average # of products or product line order per invoice

Thank You

Bobi

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Average Products per Transaction

Hi,

Write this measure and drag it to your visual

=AVERAGEX(SUMMARIZE(VALUES(Data[Pline]),Data[Pline],"ABCD",COUNTROWS(Data)),[ABCD])

Does this help?

7 REPLIES 7
Established Member

## Re: Average Products per Transaction

Hey @BobiRussell

If you have 1 invoice, there is no average that can be calculated. If you have > 1 invoices, then you can start of thinking on aggregations.

I advise you to re-think what you want to achieve. Maybe provide numbers and examples to better understand.

A

Super User

## Re: Average Products per Transaction

Hi,

In your visual drag TransactionID.  Write these measures

Items sold = DISTINCTCOUNT(Data[ItemID])

Average revenue = AVERAGE(Data[Gross revenue])

Frequent Visitor

## Re: Average Products per Transaction

Hi A

So your correct and I run this at an aggregate not at an invoice. I've attached additional info. As you can see I have \$5,360,423 in sales per month. Those sales are made up of 8,426 orders. The average Gross Revenue per order is \$614 (\$5,360,423 / 8,426) Average line items per order are 15.4. However, i'm not sure how to calculate the average disticnt product lines that were on each of the 8,426 invoices. I've added the DAX for all of the items for review. The "Avg # of Product Line per TransID" is incorrect since it only looks at the distinct product lines that were ordered as a whole not by transaction (or invoice). My previous post was on an invoice level and the thoughtes were that if i can come up an everage at an invoice I can then start building off of that DAX.

Gross Revenue = SUMX (facts_transactionTraverse[QtySold] * facts_TransactionTraverse[UnitPrice])
Order Count = DISTINCTCOUNT(facts_TransactionalTravese[TransactionID])
Avg Gross Revenue per Order = AVERAGEX( VALUES( facts_TransactionalTravese[TransactionID] ), [Gross Revenue] )
Avg Lines Items per Order = AVERAGEA(facts_TransactionalTravese[LineNumber] )
Avg # of Product Line per TransID = DISTINCTCOUNT(facts_TransactionalTravese[Pline])
# of Line Ordered on Web = CALCULATE( DISTINCTCOUNT( facts_TransactionalTravese[TransactionID]), FILTER( facts_TransactionalTravese, facts_TransactionalTravese[WebSale] = "Yes" ) )
% of Lines Ordered on the Web = DIVIDE( [# of Line Ordered on Web], [Order Count] )

Bobi

Frequent Visitor

## Re: Average Products per Transaction

Hi Ashish,

Thank you for the DAX. I've attached additional info. As you can see I have \$5,360,423 in sales per month. Those sales are made up of 8,426 orders (invoices). The average Gross Revenue per order is \$614 (\$5,360,423 / 8,426) Average line items per order are 15.4. However, i'm not sure how to calculate the average disticnt product lines that were on each of the 8,426 invoices. I've added the DAX for all of the items for review. The "Avg # of Product Line per TransID" is incorrect since it only looks at the distinct product lines that were ordered as a whole not by transaction (or invoice). My previous post was on an invoice level and the thoughtes were that if i can come up an everage at an invoice I can then start building off of that DAX.

Gross Revenue = SUMX (facts_transactionTraverse[QtySold] * facts_TransactionTraverse[UnitPrice])
Order Count = DISTINCTCOUNT(facts_TransactionalTravese[TransactionID])
Avg Gross Revenue per Order = AVERAGEX( VALUES( facts_TransactionalTravese[TransactionID] ), [Gross Revenue] )
Avg Lines Items per Order = AVERAGEA(facts_TransactionalTravese[LineNumber] )
Avg # of Product Line per TransID = DISTINCTCOUNT(facts_TransactionalTravese[Pline])
# of Line Ordered on Web = CALCULATE( DISTINCTCOUNT( facts_TransactionalTravese[TransactionID]), FILTER( facts_TransactionalTravese, facts_TransactionalTravese[WebSale] = "Yes" ) )
% of Lines Ordered on the Web = DIVIDE( [# of Line Ordered on Web], [Order Count] )

Thank You Ashish!

Bobi

Super User

Hi,

Frequent Visitor

## Re: Average Products per Transaction

Oka,

So here is a sample detail of 3 TransID (Invoices) that I ‘m providing for reference.

The below is a summary of that raw data. I took the raw data and calculated the distinctcount PLine for each TransID.

So below is the calculation that I wanted to get at. I took the # of Distinct Pline of each TransID and took the average of the three samples provided.

I hope that this info is easy to follow and anyone needs more info or details I can share that as well.

Thank You,

Super User

## Re: Average Products per Transaction

Hi,

Write this measure and drag it to your visual

=AVERAGEX(SUMMARIZE(VALUES(Data[Pline]),Data[Pline],"ABCD",COUNTROWS(Data)),[ABCD])

Does this help?