cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a68tbird Member
Member

Sales Percentage of Total Available Inventory

Hello All -

   I've run into a block here trying to understand filter context and relationships.  I have a SalesTable with everything one would expect to find in a Sales Table:

 

SalesTable 

OrderIdSKUQuantityTicketSectionId
orders/112246NTL2639-44511ticketsections/4451
orders/112246NTL2639-44511ticketsections/4451
orders/114866NTL2639-44511ticketsections/4451

 

and an Inventory table which has the total allotment of units available per SKU. 

 

SkuInventoryTable

IdTicketSectionIdTotal
inventoryrecords/3678ticketsections/445110

 

I'd like my report to show sales of 30% - 3 tickets sold (SalesTable) of the 10 alloted to inventory (SkuInventoryTable). The relationship between the two tables is easy enough - on TicketSectionId - as a many to one.

 

I have a matrix on the canvas that can drill down to the ticket sections (there's more info in the SalesTable than what I'm showing), which shows the total sales for each section. I'd like to include the Total amount from SkuInventory as well as the sales percentage. This is where I get lost.  The matrix is summing my sales totals, but I can't just drop in the SkuInventory Total because the filter context is wrong. 

 

Hope I've explained this clearly enough. Any help/suggestions are very welcome.

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
a68tbird Member
Member

Re: Sales Percentage of Total Available Inventory

Relationship looks to be solid.

 

I did get the number I'm looking for returned with this measure:

 

Inventory =
CALCULATE ( SUM ( SkuInventory[Total] ), RELATEDTABLE ( TicketSales ) )

Putting this measure into my matrix along with the sales total measure gives me exactly what I need.

 

 

7 REPLIES 7
Floriankx Established Member
Established Member

Re: Sales Percentage of Total Available Inventory

Try to create a Quantity measure:

Quantity:=Sum(SalesTable[Quantity])

 

and a Total Quantity:=Sum(SkuInventoryTable[Total]

Then you should be able to build a Ratio:=Quantity/Total Quantity

 

Give it a try and let me know.

 

Best regards.

Highlighted
a68tbird Member
Member

Re: Sales Percentage of Total Available Inventory

Thanks Floriankx for the quick reply, but I'm afraid that solution doesn't work. It's still not taking into account the filter context of each ticket section. That measure just gives me the grand total of the "Total" column. When I use that measure in my matrix, it's that grand total value that gets populated throughout, instead of what the total is per ticket section.

Floriankx Established Member
Established Member

Re: Sales Percentage of Total Available Inventory

There should be two filter contexts for ticket section. Did you try both?

a68tbird Member
Member

Re: Sales Percentage of Total Available Inventory

Sorry, what do you mean, did I try both filter contexts for ticket section?  

 

I created two measures:

TotalSalesQuantity = SUM(TicketSales[Quantity]) 

TotalInventory = SUM(SkuInventory[Total]) 

 

As you can see in the screenshot below, the TotalInventory measure does not filter to the ticket section - it's just the grand total of all ticket inventory throughout the entire matrix. 

 

CaptureMatrix.JPG

Floriankx Established Member
Established Member

Re: Sales Percentage of Total Available Inventory

This usually appears if the relationship doesn't work properly. Please check relationship first.

a68tbird Member
Member

Re: Sales Percentage of Total Available Inventory

Relationship looks to be solid.

 

I did get the number I'm looking for returned with this measure:

 

Inventory =
CALCULATE ( SUM ( SkuInventory[Total] ), RELATEDTABLE ( TicketSales ) )

Putting this measure into my matrix along with the sales total measure gives me exactly what I need.

 

 

Floriankx Established Member
Established Member

Re: Sales Percentage of Total Available Inventory

Hello,

 

this looks good. I haven't thought to use RELATEDTABLE in this issue, but I will know in futere.

 

Inventory =
CALCULATE ( SUM ( SkuInventory[Total] ), TicketSales)

 

Maybe you could check if it also works without the RELATEDTABLE. Would be nice to know.

 

Best regards.