cancel
Showing results for
Did you mean:
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

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
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
Highlighted
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.

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.

Established Member

## Re: Sales Percentage of Total Available Inventory

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

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.

Established Member

## Re: Sales Percentage of Total Available Inventory

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

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.

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.