Calculating Product Sales Gains

Hi All

I have quite a complex problem and I’m not sure how to approach this or it’s even possible in Power BI. Apologies for the lengthy post but I’m not sure how else to best explain the scenarios.

I need to calculate product sales gained based on products identified during visits to customers compared to previous visits and any product that may have been ordered at the time of the most recent visit. It’s a little hard to explain so I have put together some scenario examples below which I’m hoping will help explain what I’m trying to achieve.

The required outcome is the need to be able to report on product gains by customer and product for selected periods. Identifying product losses would also be useful.

Some basic rules:

  • A product is deemed to have been ‘gained’ if it did not exist on a shelf (a facing) on a prior visit but does exist in the most recent visit.
  • A product is also ‘gained’ if it did not exist on a shelf on a prior visit, but an order for that product was taken.
  • A product in NOT gained if it already exists on a shelf and an order is taken for the same product.
  • The actual quantity on shelf or ordered is not so important - it’s more about the product gains.

Visit data is in a table (Visits) and sales are in a separate table (Sales). At present I have a couple of SQL Views that present the data as pseudo fact tables (FactVisits and FactSales) to Power BI - I don’t have any ETL logic building real fact tables right now.

The scenarios below relate to interactions with one customer.

Scenario 1: First visit to a customer and they have no existing products on the shelf - an order is taken. All ordered products = gain.

Scenario 2: Visit to a customer (first or otherwise) and products are discovered on the shelf. An order is taken for existing and new products. Only the ordered products that aren’t on the shelf are considered gains.

Scenario 3: Visit to a customer (first or otherwise) and products are discovered on the shelf. The products discovered differ to those identified on the prior visit. The products that did not exist on shelf in previous visit are considered gains. No order is taken in this example.

Scenario 4: Same as scenario 3 except an order is taken at the time of the most recent visit. The products that did not exist on shelf in the previous visit AND the products ordered that do not already exist on shelf are considered gains.

Here’s an image of the Power BI relationships in my test model. Unfortunately, I cannot upload this as it has confidential data. I can prepare an anonymized version if necessary.

I have full control over the SQL database and what can be loaded into Power BI.

Has anyone done anything similar or could offer some guidance on how best to approach this? I’d prefer to be able to do this in DAX if possible.

Many thanks in advance

Hi @t.irani,

Look into churn analysis as basic pattern

If you need any further assistance please provide a sample PBIX and mock up of expected results.

I hope this is helpful

Hi Melissa, thanks for your reply.

I have already watched that video and didn’t quite see how it fits my requirement - this focuses on sales relating to one value, a date period.

I have three things to take into consideration; comparing current visit products, previous visit products and ordered products to determine the gain status.

I have been trying to understand the concepts behind virtual tables, would using these be relevant here?

I will get a pbix setup as soon as I can.


Thanks for posting your question @t.irani. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hi @t.irani, we’ve noticed that no response has been received from you since the 26th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

In hindsight, I think the detail here is too complex. I’ll break it down to smaller chunks and create a new topic.