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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a68tbird
Resolver II
Resolver II

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

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.

 

 

View solution in original post

7 REPLIES 7
Floriankx
Solution Sage
Solution Sage

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.

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.

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

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

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

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.

 

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.