Please, I need your help in calculating the average sales values per transaction within a cross-selling basket.
So, for example, let us assume using your approach product A, cross-sell with B, C, D. in a way that 10 customers buy A with B, 15 customers buy A with C and 2 customers bus A with D.
Can we calculate the average sales value and quantities sold per transaction within each cross-selling basket (i.e let’s say the customer bought 1 quantity of A @$5 with 1 of B @ $3 average sales per transaction is 8/2 = 4 while avg quantity per transaction is 2/2=1)
If it is also possible to calculate the total average for all the cross-sell that occurs within A
Please provide a work-in-progress PBIX file and a sample dataset and mock-up of your expected results in Excel for the forum members to investigate your issue.
Also, calling-out specific members is discouraged, especially in initial post, as other members may feel excluded and decline to participate.
Thanks for the information. It really helped me mask my data. kindly find attached the Pbix file for the cross selling analysis.
These are my request:
Firstly, please, I want this analysis to be done per product group and not per Item ID.
Secondly, lets assume I don’t want to create a separate orders table and I want to do this analysis on only two sales channels(4.01 and 104.1). However, on the orders table we have 3 sales channels (4.01 , 104.1 and 12). Please, exclude sale channel 12 from the analysis and only base it on sales channel 4.01 and 104.1
Lastly, I would like to calculate average quantities sold per transaction and average sales value per transaction within each basket.
Transaction: All orders IDs that transact within a Basket
Basket: A product group with all other products it cross-sell with
Could you please give an exact definition of “Cross-selling”, preferably using header titles from your sample PBIX provided ?
How do you come to the numbers below, and what does Group1-100140 vs 1-100140 with outcome 34 mean ?
Be aware that the sample data provided in the PBIX does not contain customer data.
I tried a cross-selling definition as selecting only the multiple products ordered per order, selected for sales channels 4.01 and 104.1, but the result, see print screen below, is meaningless, only $ 556 revenue.
Also you have “Order type”, being Orders and Returns, how does this interact with your Cross selling ?
Thanks for the attempt. I followed the Enterprise DNA methodology but I guess yours is a bit different.
The number in the table are just fictitious numbers for the cross-selling. I had to mask the data because they are corporate data. Group1-100140, Group1.70100, and all are like product names or product groups that cross-sell with one another.
Consider the orders table as the fact table while the product, order types and sales channels are the dimension table.
The orders table is being used for several other analysis,in which all sales channels and order types are needed. However, for cross-selling, I only need sales channels 4.01 and 104.1 and order type 1. So what I did was to duplicate the orders table and then do the ETL to filter for the specific sales channels and order types.
So, I said please, exclude sale channel 12 and order type 3 from the data to be used for the analysis to know if it is possible to do the cross-selling analysis from the same orders table used for other analysis without necessarily duplicating the order table and still be able to exclude sales channel 12 and order type 3 within the cross-selling analysis
So from the screenshot, a total of 212 customers, which in this case are order IDs bought Group1-100140. Within this group, it means that 56 customer IDs that purchased Group1-100140 also bought Group1-70100. 32 order IDs that bought Group1-100140 also buy Group2-17024. This is the exact definition of cross-selling.
Using the enterprise DNA cross-selling example, I developed the total number of Order IDs per basket(a product that cross-sells) and the percentages. However, I also want to calculate the average quantities sold per this basket and the average value of the transactions (selling price x quantities) within each basket which are the text in red.
I hope my explanation helps a bit. The topic is a bit too difficult to explain with text but I hope you understand it better now.
Regrettable still have questions remaining: (so far it is not possible to proceed)
“Cross-selling opportunities” as explained by EDNA, see link below, make use of customers, and analyses which customers bought multiple products.
How does your explanation above exactly relate to the Fakt table provided in the sample PBIX, which does NOT have customers, but only orderIDs (see below) ??
How do you come to 212 customers and 56 customers ?
My source is your “Cross-Selling.pbix” (842,3 KB), which does not contain this information ???
The (Product-) Item ID can be grouped into Product Group (like Product Group 1-100140).
But with which dimensions from your Fact-table you want to do the Cross Selling analysis, there is no customer ID, and there are many, many Order IDs ??
That is why I asked you : " Could you please give an exact definition of “Cross-selling”, preferably using header titles from your sample PBIX provided ?
So far it is regrettable not possible to proceed.
Kind regards, DS
Thanks again for the feedback. I think my explanations have been terrible. Sorry about that. Please use the newly attached Pbix file with a sample of the cross-selling I had prepared.
Now what I want specifically is to replicate the cross-selling Absolute value table on the Pbix file but to have the cross-selling result as average quantities sold and average transaction value (quantities + price) per each cross-selling basket.
First of all, it would be much easier for this kinds of issues to speak each other instead of using a forum, to better understand, but that is not possible.
Could you confirm that you are looking for orders with multiple order lines (the cross selling) ? Is this the concept for your requested outcomes ?
Do you have a audit trail in place ( are you sure the outcomes, like 60 for P10-460 below are correct ?)
I tried myself, not sure : When I added the OrderID above the Product PGroup in the rows of your table “Cross selling Countrows”, for order 100064 two productgroups appear, but in fact only one product was sold ? Possible the granularity of the table, adding the OrderID dimension, and the Countrows DAX measure does not match.
2 MEASURES are added:
I made two additional measures, started with “DS”, see attached PBIX, for your requested Quantity and Average Price (see below part of it).
Auditing the Quantity numbers adding the dimension OrderID to the table does not give clear answers.
Exporting the Quantity table into Excel, the total quantiy is 66.213 (multi order line units sold), which aligns more or less with the additional order lines from the order source data, filtered for sales channels, of in total 59.161
Hope this is already your required answer, give me your thoughts.
Yes, it would have been easier to explain verbally than with text but its impossible.
However, thank you so much for the support. You solved 99.99% of my problem.
Yes, I was looking for order with multiple order line but intend to calculate their quantities and average value so your additional measures worked perfectly with what I want.
The only thing I added was to add a custom column (qty * price) on power query in order to calculate the sales value per order which enables me to calculate the average sales value per cross-sold orders.
Please do you have a sample of an audit trail in Power BI? Is there a special way of setting this up?
I sincerely appreciate every seconds you spared me to answer my request. It was really a solution I had to deliver at work ASAP and you bailed me out. I sincerely appreciate @deltaselect .
I did some basic work, see attached the measures starting with DS1 (3) and DS2, see also attached PBIX, I tried to audit the total lines of cross selling, which is defined as order lines per order >1, being the difference between distinct-count of the orders and total order lines (countrows), expected to be 33.793, based on table Orders-2
Next step is to find this number in the Count rows matrix, I did it on product ID level, see page “Audit Countrows”, with measure “DS2 Audit QTY Multiple products”, based upon Countrows. (very slow to run!)
Advantage running this on product ID, is that hereby the orderID can be added to the row dimension in the table, (very slow without a specific order),
With order 100021, who has 3 order lines, you can see an order within the matrix report, so you can check this with the Orders-2 table.
I downloaded the table results to Excel, as the totals in the Power BI are yet not correct, in Excel with a Pivot table they are correct, see attached:
The total of the table appears have to be divided by two ( double shown values in the matrix, by row and by column, see product-ID 1131 for example) , but still differs significantly from the expected outcome 33.793 ( in Excel showed 81.908 /2 = 40.908, which is based upon the table Orders-2)
If those figures aligns, I would be very confident about using the quantity and average prices, as explained before.
Maybe you have a simple explanation for the difference of those two count-rows approaches.
I consider Audit trails as a very important part of building reliable reports.
Thanks for your insight as regards the Audit trail. I usually do it manually as well; I just thought there could be a different approach.
From the xls file you attached, the approach to our audit was the same, but I think it was incorrect on yours because an important table was omitted in the datamodel since I masked the data and excluded some tables
I did the audit on the actual datamodel, and the values were correct.
For example, there is another table called order-details table which is similar to orders table but it is at order level.
This means the order-details table contains the total value of the order uniquely.
So if there is order ID 1 with 2 different products valued at $5 each on the orders table, order ID1 will be duplicated. However, on the orders-details table, there will be unique orderID1 with $10 as its value.
Therefore, the order IDs are the primary key on order details and connect with the orders table (1-many relationship).
This relationship helped me carry out the audit trail using the unique order IDs from the order details table.
Summarily, your Dax calculation was correct and the audit would have been correct if you have access to the actual datamodel.
Thanks so much for sparing your time to attend to my request. I hope we do more together. This is such an interesting platform to have an educative conversation.
