Forum Discussion

BobiRussell's avatar
BobiRussell
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Write this measure and drag it to your visual

     

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

     

    Does this help?

Anonymous's avatar
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's avatar
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