Sales Percentage of Total Available Inventory

[a68tbird]

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 

OrderId	SKU	Quantity	TicketSectionId
orders/112246	NTL2639-4451	1	ticketsections/4451
orders/112246	NTL2639-4451	1	ticketsections/4451
orders/114866	NTL2639-4451	1	ticketsections/4451

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

SkuInventoryTable

Id	TicketSectionId	Total
inventoryrecords/3678	ticketsections/4451	10

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.

[Floriankx]

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.

[a68tbird]

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]

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

[a68tbird]

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. 

[Floriankx]

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

[a68tbird · Accepted solution]

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]

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.