Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |