Need assistance calculating 'Products Allocated' vs 'Sales the following day' by SKU by Merchant -

[AlexAlberga727]

Thanks for reviewing this post as I need assistance calculating the following - 

First let me provide some background:

In my position I am allocating eCommerce Retailers a daily authorized quantity of units that can be sold on their platforms.

For example - If we allocate QTY:100 to Amazon. I want to compare this allocation to their sales the next day for that SKU.

ex. 100 Allocated vs 80 Sold. I believe this will provide me with a utilization percentage. IF Accounts hit 100%, then we will need to increase the allocation.

I have a transaction sheet which inlcudes sales by day by retailer. I have collected the # sales per unit, and have a secondary table which includes the allocations to the associated retailer.

Below is a screenshot of my current output - which is incorrect. It is providing me with the total number of units sold for the day opposed to the number of units sold for that specific productID.

Let me know your thoughts and If more information is needed, let me know!

Thanks in advance!

[JarroVGIT]

Hi @AlexAlberga727 

This is very much possible. I am assuming the following table structure for the transactions:

Date

Retailer

ProductID

Sales Qty

Your calculated column in the Allocations table would be:

SoldNextDay = 
VAR nextDayDate = Allocations[Date]+1
VAR curRetailer = Allocations[Retailer]
VAR curProductID = Allocations[ProductID]
RETURN
SUMX(FILTER(Transactions, Transactions[Date] = nextDayDate && Transactions[Retailer] = curRetailer && Transactions[ProductID] = curProductID), Transactions[Sales Qty])

You can easily create a utilitzation from this by doing something like this:

SoldNextDay = 
VAR nextDayDate = Allocations[Date]+1
VAR curRetailer = Allocations[Retailer]
VAR curProductID = Allocations[ProductID]
VAR curAllocation = Allocations[Allocation]
VAR soldNextDay = SUMX(FILTER(Transactions, Transactions[Date] = nextDayDate && Transactions[Retailer] = curRetailer && Transactions[ProductID] = curProductID), Transactions[Sales Qty])
RETURN
DIVIDE(soldNextDay, curAllocation)

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[AlexAlberga727]

@JarroVGIT  Thanks so very much for assisting and putting together a solution.

However, I am going to change the context slightly, which will change the required solution.

Instead of calculating a 'NextDay' value. I would like to calculate what the past allocations were depending on the date range Im slicing by. To briefly explain - I want to see the total number of units sold by SKU by Retailer and what their allocations were.

I will want to review either a specific day's or week's units sold, and compare that to the allocations given.

IF I am reviewing a specific day's units sold  - I would like to see the prior day's allocation.

IF I am reviewing a week's units sold - I would like to see the week's allocation.

Then calculate the utilization accordingly - Which I believe would be just as simple as [Units Sold] / [Allocation].

You were correct in assuming the structure of my transaction table. Below is the structure of the Allocation Table.

Im hoping to create a report with the below structure - 

I apologize for changing the context here. However, thanks in advance. 😃 

[JarroVGIT]

That is possible but be aware that you have two logics in there. If you select a day you want to compare allocation vs next day sold. If you select more than a day you compare exactly the two periods. Is my understanding correct? (I am not at my computer right now so will provide an solution for you tomorrow)

[JarroVGIT]

Actually come to think of it, the solution builds on our previous solution. Now we have a table with all sold and allocated values, you can create a measure like this
Total Sold := SUM(Allocations[unitssold])
Total allocation := SUM(Allocations[Allocation])
Utalisation := [Total Sold]/[Total allocation]

Now create a table visual with retailer and productid from Allocations plus the three above measures. Add a date sliver to be flexible in selecting date ranges 🙂

[AlexAlberga727]

So - This was my original approach. Sounds easy, and I may very well be missing something here. However, My "Prior Days Allocation" values are being calculated incorrectly.

I have attempted using the following DAX measures:

Total Allocation = SUM( fAllocations[Allocation] )

PD Total Allocation = CALCULATE( [Total Allocation] , DATEADD( dDateTable[Date], -1, DAY )

When I attempt to use either measure it is returning the total allocation for the day, and is not taking the retailer, or SKU into consideration.

[JarroVGIT · Accepted solution]

Hmm that is weird behaviour indeed!

Just to be sure; you are following these steps:

1. Add the calculated column to the Allocations table by the given DAX:

SoldNextDay = 
VAR nextDayDate = Allocations[Date]+1
VAR curRetailer = Allocations[Retailer]
VAR curProductID = Allocations[ProductID]
RETURN
SUMX(FILTER(Transactions, Transactions[Date] = nextDayDate && Transactions[Retailer] = curRetailer && Transactions[ProductID] = curProductID), Transactions[Sales Qty])

2. Add the following measures to the Allocation tables:

Total Sold = SUM(Allocations[SoldNextDay])
Total Allocation = SUM(Allocations[Allocation])
Utalisation = [Total Sold]/[Total Allocation]

3. Add a Datetable ( DateTable = CALENDARAUTO() ) to your model and create a relationship between Allocation[Date] and DateTable[Date].

4. Create a Table visual with (from table Allocation) column Retailer, ProductID, and the three measures. Add a Slicer based on DateTable[Date] column.

This really should work. If you want you can PM me a link to your PBIX and I will have a look for you why this is not returning the correct results.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[AlexAlberga727]

Looking forward to going back and forth with you tomorrow. Until then, enjoy!

But to correct you above - and to make things more simple - 

I want to review a single days units sold - by retailer, by SKU vs the prior days products allocation. Same products are sold across multiple retailers, and each retailer may have a different allocation.