Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BobiRussell
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

Hi,

 

Write this measure and drag it to your visual

 

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

 

Does this help?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
MAAbdullah_47
Helper V
Helper V

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:

MAAbdullah_47_0-1607428817447.png

 

 

 

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  it is the same topic of this thread (Average Products per Transaction). 

As i said in my previous post, try it on your original data.  That is the best wy to know whether your formula will work fine or not.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

In your visual drag TransactionID.  Write these measures

 

Items sold = DISTINCTCOUNT(Data[ItemID])

Average revenue = AVERAGE(Data[Gross revenue])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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,

Hi,

 

Write this measure and drag it to your visual

 

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

 

Does this help?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.