Forum Discussion

ells's avatar
ells
Helper II
3 years ago

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

  • halfglassdarkly's avatar
    halfglassdarkly
    Responsive Resident
    3 years ago

    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

    • ells's avatar
      ells
      Helper II
      3 years ago

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