Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |