Forum Discussion

EricPad's avatar
EricPad
Regular Visitor
2 years ago
Solved

DAX calculations totals by group by -multiple columns

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

 

 

 

 

 

 

DAX
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      EricPad
      Regular Visitor
      2 years ago

      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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.