cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BobiRussell Frequent Visitor
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.

 

image.png

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.

 

image.png

 

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
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
AClerk Established Member
Established Member

Re: Average Products per Transaction

Hey @BobiRussell 

I think your business goal is a bit off.

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

BobiRussell Frequent Visitor
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] )
 

image.png

Thank You in advance for your help!

Bobi

BobiRussell Frequent Visitor
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] )
 

image.png

Thank You Ashish!

Bobi

Super User
Super User

Re: Average Products per Transaction

Hi,

 

Please build a simple example with small numbers and show your exact answer you are expecting.

BobiRussell Frequent Visitor
Frequent Visitor

Re: Average Products per Transaction

Oka,

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

 

image.png

 

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

 

image.png

 

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.  

 

image.png

 

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
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?