cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Stock usage over time

Hi everyone,

I am trying to calculate stock usage per day to make ordering easier.

I have several tables:

1. Ingredients

3. Date Table

4. Recipes Table

My goal is this manual Excel example below:

 08 08 2019 09 08 2019 10 08 2019 11 08 2019 12 08 2019 13 08 2019 14 08 2019 15 08 2019 16 08 2019 17 08 2019 18 08 2019 Soup Roasted Pumpkin Soup Roasted Tomato Ciabatta Rolls W80 ZOOP Brown Large Bag S0.20 ZOOP Brown Roll Bag S0.02 Serviette 2 Ply

Relationships are:

Ingredients (1-Many) Recipes

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

## Re: Stock usage over time

Hi @TomTank1504 ,

Maybe you can try this:

1. Do not create relationships using 'Menu Table', and create 'Many-to-Many' relationship between 'Recipes Table' and 'Menu Item Sales Table' instead.

2. Create a measure.

```Ingredient Sales =
SUMX (
ItemSales,
CALCULATE (
MAX ( ItemSales[Qty Sold] ),
ALLEXCEPT (
'Granular Recipes',
'Granular Recipes'[Item Name],
'Granular Recipes'[Ingredient]
)
)
* CALCULATE (
MAX ( 'Granular Recipes'[Quantity] ),
ALLEXCEPT ( ItemSales, ItemSales[Date], ItemSales[Item Name] )
)
)```

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Senior Member

## Re: Stock usage over time

Hi @TomTank1504 ,

Can you give me some samples of each tables? So that I can create a complete data model.

Best Regards,

Icey

Senior Member

## Re: Stock usage over time

Hi @TomTank1504 ,

Maybe you can try this:

1. Do not create relationships using 'Menu Table', and create 'Many-to-Many' relationship between 'Recipes Table' and 'Menu Item Sales Table' instead.

2. Create a measure.

```Ingredient Sales =
SUMX (
ItemSales,
CALCULATE (
MAX ( ItemSales[Qty Sold] ),
ALLEXCEPT (
'Granular Recipes',
'Granular Recipes'[Item Name],
'Granular Recipes'[Ingredient]
)
)
* CALCULATE (
MAX ( 'Granular Recipes'[Quantity] ),
ALLEXCEPT ( ItemSales, ItemSales[Date], ItemSales[Item Name] )
)
)```

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Stock usage over time

Thank you SO much - that worked perfectly.

If I should post this as a new query, let me know, but I'm trying to work out the average usage of specific ingredients (broken down using a slicer) per weekday. When I try an AVERAGEX function it returns the average for the sum of all the Sundays, for example, meaning it shows the usage for the Sundays for the whole month instead of an average Sunday.

Any clues?

Regards,

Tom

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 124 members 1,503 guests
Recent signins: