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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors