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 invoiceThank YouBobi

Hi, Write this measure and drag it to your visual =AVERAGEX(SUMMARIZE(VALUES(Data[Pline]),Data[Pline],"ABCD",COUNTROWS(Data)),[ABCD]) Does this help?

Average Products per Transaction | Microsoft Fabric Community

14 Replies

Anonymous
Not applicable
7 years ago
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
- BobiRussell
  Frequent Visitor
  7 years ago
  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] )
  
  Thank You in advance for your help!
  Bobi
Ashish_Mathur
Super User
7 years ago
Hi,

In your visual drag TransactionID. Write these measures

Items sold = DISTINCTCOUNT(Data[ItemID])

Average revenue = AVERAGE(Data[Gross revenue])
- BobiRussell
  Frequent Visitor
  7 years ago
  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
  - Ashish_Mathur
    Super User
    7 years ago
    Hi,
    
    Please build a simple example with small numbers and show your exact answer you are expecting.
MAAbdullah_47
Helper V
5 years ago
Hi Ashish_Mathur

Thank you for your clarification , I have similar case I need your advice about it, please look at the following Schema and measures:

Total Products = SUMX(SUMMARIZE(VALUES(Sales[Product Index]),Sales[Product Index],"ABCD",COUNTROWS(sales)),[ABCD])

Average Products Per Order =
DIVIDE([Total Products],[Total Orders],0)

Average Products Per Customer = DIVIDE([Total Products] , [Total Customers],0)
Total Orders = DISTINCTCOUNT(Sales[OrderNumber])
Total Customers = DISTINCTCOUNT(Sales[Customer No])

Is the following measures is correct (Average Product Per Order , Average Product Per Customer)?
- Ashish_Mathur
  Super User
  5 years ago
  Try it on your actual data and if you get the correct answer, then all's OK. If not, then share some data, explain the business question and show the expected result.
  - MAAbdullah_47
    Helper V
    5 years ago
    Hi Ashish_Mathur it is the same topic of this thread (Average Products per Transaction).