Forum Discussion
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 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
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
5 Replies
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
Hi,
Do you not have a Date column in your dataset? If you do, the measures becomes very simple.
That date column would have made the measure simpler but if you already have a solution, it is fine.
