Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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.
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |