DAX calculations totals by group by -multiple columns

[EricPad]

Hello Community - 

I am working on an Inventory Resource / Materials Resource Planning report and I am struggling to get some of my calculations working. 

Data is coming from an ERP system (Sage) and is using Sales order header and detail tables and Bill of material tables. 

The sales orders contain Items on order. The Bill of materials tables contains hierarchical data of Item and then N levels of components. I needed to include the sales order data to come up with the Date ranges of orders and quantities of orders. I did a ton of data preparation in the SQL Server tables and with stored procedures. 

Essentially I have to come up with materials required from a set of sales orders and bills of materials. I need to subtract current item quantity on hand values from the item level and then each sub item (component). I have the current on hand values in a related table in my power bi model. 

I am trying to come up with Quantity ordered by Item Code / Component Item Code Groups. I will then use that value to compare against on hand values to determine if I need to go down to the next component item level. 

How would I come up with either a calculated column or measure to get a quantity ordered by Item Code & Component Item Code groupings. 

An example of this is here:

In this example , it should be a quantity ordered of 

My visual looks like this:

Ultimately the challenge is that the quantities ordered are at the Item level but I need to display inventory and materials required at the Component Item level and filtered by date, product line, and Is Forecast or not. 

A copy of my PBIX can be found here: https://drive.google.com/drive/folders/1zyXpvaBYeZQvekOUYcF2EhaSOamkf0Da?usp=sharing

Thank you very much in advance for any help!

Eric

[v-tangjie-msft · Accepted solution]

Hi @EricPad ,

Thanks lbendlin  for the quick reply. I have some other thoughts to add:

We can create a measure.

Measure =
CALCULATE (
    SUM ( tbl_InvetoryResourcePlanning[QuantityOrdered] ),
    FILTER (
        ALL ( 'tbl_InvetoryResourcePlanning' ),
        [ComponentItemCode] = MAX ( 'tbl_InvetoryResourcePlanning'[ComponentItemCode] )
            && [ShipExpireDate]
                IN VALUES ( 'tbl_InvetoryResourcePlanning'[ShipExpireDate] )
                    && [IsForecastAcct]
                        IN VALUES ( 'tbl_InvetoryResourcePlanning'[IsForecastAcct] )
                            && [ComponentProductLine]
                                IN VALUES ( 'tbl_InvetoryResourcePlanning'[ComponentProductLine] )
    )
)

Then the result is as follows.

Best Regards,

Neeko Tang

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

[lbendlin]

Challenge yourself on the data model

Is this truly reflecting your business scenario?  Can you eliminate the bidirectional link? Should some tables be disconnected?

[EricPad]

Yes, the data model could be better but some of those tables are carry overs from previous attempts at getting this right. 

The main tables I use in my current version are:

tbl_InventoryResourcePlanning

tbl_CI_Items

MeasuresTable (just a holding table for the measures)

The model could be better but the challenge is that the requirement calls for aggregating quantities at the component level while the only way to determine date ranges is at the sales order level. 

Sales Order has many lines (and also contains date and if forecast or actual order, which are both slicers/filters)

Each line has an Item with N quantities

Each Item is made up of a Bill of materials 

A Bill of materials contains N components each with a certain amount of each required to produce the item

A component on the bill of materials is itself an Item

The Items Table CI Items contains the Product Line (another filter / slicer) and current inventory on hand