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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
amalsperera
Frequent Visitor

Dax to sum value grouped by two columns

Hi,

 

I have the following stock table

 

DateMaterialBatchStockvalue
9/13/2023A2100
9/12/2023A2150
9/10/2023B175
9/9/2023A2200
9/5/2023A15

 

A stock record will only be written if there is a transaction for that stock.

 

I need to get the total value of the stock. 

 

Eg. if I check the stock value for 9/13/2023 for Material A the value should by 105 as Batch number 1 will have a value 5 and batch number 2 will have a value 100. 

 

How do I get this done using Dax. 

 

Thanks in advnce

 

Amal

1 ACCEPTED SOLUTION

Hi @amalsperera,

I've updated it a little bit. Please check if it works correctly - https://www.dropbox.com/scl/fi/d45372koa4xjr3qfylqo7/Sample.pbix?rlkey=nac40bon3nggko254pg36toee&dl=....

I assume you don't need Total. If you do, another update will be needed.

View solution in original post

8 REPLIES 8
barritown
Super User
Super User

Hi @amalsperera,

Here's an option how to solve your problem:

barritown_0-1695724517823.png

And in plain text for convenience:

Measure = 
VAR _maxDate = MAX ( [Date] )
VAR _tbl = 
    ADDCOLUMNS ( 
        SUMMARIZE ( FILTER ( ALL ( Data ), [Date] <= _maxDate ), [Material], [Batch], "Latest Date", MAX ( [Date] ) ),
        "Stockvalue",
        VAR _currentMaterial = [Material]
        VAR _currentBatch = [Batch]
        VAR _currentDate = [Latest Date]
        RETURN MINX ( FILTER ( ALL ( Data ), [Material] = _currentMaterial && [Batch] = _currentBatch && [Date] = _currentDate ), [Stockvalue] ) )
RETURN SUMX ( _tbl, [Stockvalue] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Hi @barritown ,

 

Could you please attach the pbix. I am getting the total stock value as the value for all items. 

 

Thanks 

 

Amal

Sure, here it is 

https://www.dropbox.com/scl/fi/5t7xyxzujap3qh86ripgq/community-stock.pbix?rlkey=0cg0kaicaalc2kr5wkpl...

 

I think I forgot to mention an additional dimension table I used - you'll see in the file.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Hi @barritown ,

 

I tried it out, but it still is not giving per SKU. Could you please check the attached pbix and see what I am doing wrong?

 

https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=drive_link

 

Thanks

 

Amal

Hi @amalsperera,

Unfortunately, I cannot access the file you tried to share. Could you please double check sharing settings?

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Hi @barritown,

 

Sorry I could not respond earlier. I was out of office. Could you please try now?

 

https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=sharing

 

Amal

Hi @amalsperera,

I've updated it a little bit. Please check if it works correctly - https://www.dropbox.com/scl/fi/d45372koa4xjr3qfylqo7/Sample.pbix?rlkey=nac40bon3nggko254pg36toee&dl=....

I assume you don't need Total. If you do, another update will be needed.

Hi @barritown ,

 

Thanks a bunch. I fiddled around with the measure and got the totals as well. This is the dax for it.

 

M_Distributor_Stock =
VAR _maxDate = MAX ( DistributorInventoryValue[Date] )
VAR _tbl =
ADDCOLUMNS (
        SUMMARIZE ( FILTER(ALLEXCEPT(DistributorInventoryValue
                                             ,DistributorInventoryValue[MaterialNo])
                                             , DistributorInventoryValue[Date] <= _maxDate )
                                 , [MaterialNo]
                                 , [BatchSerialNumber]
                                 , "Latest Date", MAX ( DistributorInventoryValue[Date] ) ), "max Date" , _maxDate

                                 , "Stock_value",
                                          VAR _currentMaterial = [MaterialNo]
                                          VAR _currentBatch = [BatchSerialNumber]
                                          VAR _currentDate = [Latest Date]

                                          Return CALCULATE(MINX(FILTER(DistributorInventoryValue
                                                        , [MaterialNo] = _currentMaterial
                                                         && [BatchSerialNumber] = _currentBatch
                                                         && [Date] = _currentDate)
                                                         ,[StockValue])
                                                    ,ALL('Date')))
RETURN SUMX ( _tbl, [Stock_value] )

 

I have accepted your post as the solution as you pointed me in the right direction. I have been struggling with this for some time. 

 

Thanks again and Cheers,

 

Amal

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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