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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ells
Helper II
Helper II

Sum at the correct level

ok,
So this is more of a quaestion about how to move up a level in a dimension and maintain the sum of the components rather than doing the calculation at the highest level.

 

so I have a

table 'expired' that shows expired stock at product level (one row per product
table 'stock' that shows available stock at product level (multiple rows per product)
Measure [expired] = SUM('expired'[Qty]

to get the amount of available product I go

VAR NumberofRows =SUMX('stock',1)
VAR AvailableStock = SUMX('stock','stock'[Quantity] - DIVIDE( [expired], NumberOfRows)



I have multiple scenarios like this where the calculation must be done at the base level and summed up. How do I do this?

Thanks

E

2 REPLIES 2
halfglassdarkly
Resolver IV
Resolver IV

If I understand you correctly the issue is the need to filter your sumx at product level?

e.g.

Var PID = SelectedValue('stock'[Product ID])

Var AvailableStock = SUMX(FILTER('stock','stock'[Product ID]=PID),'stock'[Quantity])

Var ExpiredStock = SUMX(FILTER('expired','expired'[Produt ID]=PID),'expired'[Quantity])

RETURN

//I'm not entirely sure what your example is aiming to do using Divide? If you were simply subtracting expired stock from the stock total it would look like this

Availablestock - ExpiredStock

See also the example in the Microsoft documentation for SUMX: https://learn.microsoft.com/en-us/dax/sumx-function-dax

Thanks,
Will look at that. SumX has not quite worked out as I hoped for. It is close but not spot on.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors