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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexAlberga727
Resolver II
Resolver II

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

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.

 

image.png

 

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

 

Thanks in advance!

1 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! 🙂

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @AlexAlberga727 

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

DateRetailerProductIDSales 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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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.

 

PowerBI_AU_Example_12192019(2).png

 

Im hoping to create a report with the below structure - 

 

PowerBI_AU_Example_12192019.png

 

 

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

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)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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! 🙂

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.