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,

 

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?

    • MAAbdullah_47's avatar
      MAAbdullah_47
      Helper V
      5 years ago

      Hi Ashish_Mathur  I don't know whay you make it bit confusing and complecated what do you means by (ABCD)?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        That is the title of the column that you wih to add in the SUMMARIZE() function.  Please readup on the SUMMARIZE() function.