Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
EricPad
Regular Visitor

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:

EricPad_0-1726191973480.png

 

In this example , it should be a quantity ordered of 

EricPad_1-1726192033408.png

 

My visual looks like this:

EricPad_2-1726192085362.png

 

 

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1726471436045.png

 

vtangjiemsft_1-1726471451173.png

 

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. 

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1726471436045.png

 

vtangjiemsft_1-1726471451173.png

 

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
Super User
Super User

Challenge yourself on the data model

 

lbendlin_0-1726269955474.png

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

 

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

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.