- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Sales Percentage of Total Available Inventory

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

a68tbird

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
07:51 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

a68tbird

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
10:42 AM

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

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
08:02 AM

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

Member

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
08:24 AM

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

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
08:34 AM

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

a68tbird

Member

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
08:55 AM

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

Established Member

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
09:12 AM

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

Highlighted

a68tbird

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
10:42 AM

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

Re: Sales Percentage of Total Available Inventory

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018
10:17 PM

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.