Calculate Remaining Items

[Anonymous]

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 like to create two new columns with Rem and Rem% as below:

Also, if possible, I wish to leave the Rem and Rem% blank if there is no data in Used.

#       Used     Rem     Rem%

1         20        80        80%

2         30        50        50%

3         15        35         35%

4

5

Thanks in advance for your help.

Ahmed

[Ashish_Mathur]

Hi,

Do you not have a Date column in your dataset?  If you do, the measures becomes very simple.

[Anonymous]

Hi Ashish,

Yes I do have date col as well, I wanted to keep it simple for the test case.

[Ashish_Mathur]

That date column would have made the measure simpler but if you already have a solution, it is fine.

[mahoneypat · Accepted solution]

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]

Hi Pat,

The solution works just fine.

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