Calculate Inventory Sales Value

[CalGuyer]

I have a star schema established. It includes both sales data and inventory data. I can compute the inventory value based on the COGS and put that number into a card or table. I am trying to compute the value of the inventory based on the average sale price (this year) of the items in inventory. The table works great (see illustration) because of the context. However, the card does not show the correct information. I am sure that the formula is calculating the total inventory by the total average price of all items because a context is not established. I am at a loss.

[AntrikshSharma]

If you want to mimic the behaviour of the table visual, then use SUMMARIZE with CALCULATE.

=
CALCULATE (
    [Total Sales],
    SUMMARIZE ( Sales, Products[Brand], Customer[Continent] ),
    Dates[Calendar Year Number] = 2009
)

[BrianJ]

@CalGuyer,

Welcome to the forum – great to have you here!

Per your question, please check out the thread below which talks about how to use virtual tables to create the necessary evaluation context for displaying measures like this in cards. I think a very similar approach will work well here.

See how it goes, and if you have any additional questions/issues with this, just give a shout.

I hope this is helpful.

– Brian

Using "All-in" Date Harvest Approach with Cards

I received the following interesting question regarding the Date Harvest Deep Dive video I did recently for the Enterprise DNA TV YouTube channel as part of the ongoing time intelligence series that @Melissa and I have been doing: [image] Billy is correct that the approach certainly is easier when you have an explicit evaluation context on date in the visual. However, for visuals that don’t contain this explicit evaluation context (e.g., cards, gauges, etc.), the approach absolutely still wor…

[CalGuyer]

Average Sale Price (ASP) is a calculated value (Total Sales Revenue/Total Shipped Quantity), from the Sales data, from all sales of a product derived from all invoices. We sell at different prices to different customers. Inventory of products is in another table. When I have the table providing the product context, the calculation work great. However, the card does not provide any context for the calculation. I think what I need to do is to create a table of all products sold and their respective ASP then use that table as a lookup for the Inventory calculation (like a vertical lookup table in Excel). This table needs to be dynamic because the ASP changes over time depending on the ordering habits of customers.

[Despo]

what type of relationship is established between your tables?

[CalGuyer]

Here is the model. I am trying to follow the best practices as taught by Sam and others. Hope this helps.

I calculate the ASP from the data in the sales table (Total Revenue/Total Quantity) for each of our products. The inventory table provides product quantities in our various warehouses. We have ready to sell products housed at least two warehouses.

I want to multiply the ASP by the quantity of ready to sell inventory in the various warehouses to determine the approximate sales value of our inventory. I have already calculated the approximate replacement COGS since the Average Purchase Price of our goods is contained in the Inventory table that calculation is straight forward. I can calculate the sales revenue by creating a table and multiplying the ASP by the inventory quantity. That is easy because the ASP is conditioned by the product number in that row. Putting this quantity into a card complicates the calculation because the ASP must now be dynamic.

[adesinamk]

Hello @CalGuyer

I have gone through your request. Can you please share the power bi file with all the data so that it can give more insight into what you want to achieve and I can work with that.

[EnterpriseDNA]

Thanks for posting your question @CalGuyer. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[EnterpriseDNA]

Hi @CalGuyer, we’ve noticed that no response has been received from you since the 24th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!