Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Remaining Items

Hello, Any help in how to do this in Power Bi will be highly appreciated.   Given the data below: StartCount = 100   Table1: #       Used 1         20 2         30 3         15 4 5   I ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is a measure expression to do it.  I called your table Used (so you'll need to change that to your actual table name), and I renamed your "#" column to "Num".  To get your 2nd measure, just change the return as indicated in the comment.

     

    Rem =
    VAR vInitial = 100
    VAR vThisNum =
        MIN ( Used[Num] )
    VAR vUsedSoFar =
        CALCULATE (
            SUM ( Used[Used] ),
            ALL ( Used ),
            Used[Num] <= vThisNum
        )
    RETURN
        vInitial - vUsedSoFar
    // for Rem % use DIVIDE(vInitial - vUsedSoFar, vInitial)

     

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Here is a measure expression to do it.  I called your table Used (so you'll need to change that to your actual table name), and I renamed your "#" column to "Num".  To get your 2nd measure, just change the return as indicated in the comment.

 

Rem =
VAR vInitial = 100
VAR vThisNum =
    MIN ( Used[Num] )
VAR vUsedSoFar =
    CALCULATE (
        SUM ( Used[Used] ),
        ALL ( Used ),
        Used[Num] <= vThisNum
    )
RETURN
    vInitial - vUsedSoFar
// for Rem % use DIVIDE(vInitial - vUsedSoFar, vInitial)

 

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Pat,

    The solution works just fine.

     Is there a simple way to keep the results blank if the used value is blank.