cancel
Showing results 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.

Frequent Visitor

## Dax to sum value grouped by two columns

Hi,

I have the following stock table

 Date Material Batch Stockvalue 9/13/2023 A 2 100 9/12/2023 A 2 150 9/10/2023 B 1 75 9/9/2023 A 2 200 9/5/2023 A 1 5

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.

Amal

1 ACCEPTED SOLUTION
Super User

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.

8 REPLIES 8
Super User

Hi @amalsperera,

Here's an option how to solve your problem:

And in plain text for convenience:

``````Measure =
VAR _maxDate = MAX ( [Date] )
VAR _tbl =
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

Frequent Visitor

Hi @barritown ,

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

Thanks

Amal

Super User

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

Frequent Visitor

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?

Thanks

Amal

Super User

Hi @amalsperera,

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

Best Regards,

Alexander

Frequent Visitor

Hi @barritown,

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

Amal

Super User

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.

Frequent Visitor

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 =
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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors