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?

Ashish_Mathur's avatar
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's avatar
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] )
 

image.png

Thank You Ashish!

Bobi

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

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

    • BobiRussell's avatar
      BobiRussell
      Frequent Visitor
      7 years ago

      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,

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        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?