Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 invoice
Thank You
Bobi
Solved! Go to 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?
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)?
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.
Hi @Ashish_Mathur again Kindly can you look at this post : https://community.powerbi.com/t5/DAX-Commands-and-Tips/Knowing-The-Number-Of-Days-Between-1st-or-Nth...
when you become free?
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.
Hi,
In your visual drag TransactionID. Write these measures
Items sold = DISTINCTCOUNT(Data[ItemID])
Average revenue = AVERAGE(Data[Gross revenue])
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.
Thank You Ashish!
Bobi
Hi,
Please build a simple example with small numbers and show your exact answer you are expecting.
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,
Hi,
Write this measure and drag it to your visual
=AVERAGEX(SUMMARIZE(VALUES(Data[Pline]),Data[Pline],"ABCD",COUNTROWS(Data)),[ABCD])
Does this help?
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.
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.
Thank You in advance for your help!
Bobi
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |