Forum Discussion
Using a filter with no relationship
Here is one way to do this. It assumes there is no relationship between the two tables, but it could be modified if that is the case too. I added an Index column in query on your Deposits table to be used in the measure, to get the total amount of previous deposits (DepositOrder column).
Spend FIFO =
VAR totalspend =
SUM ( Spend[Spend] )
VAR thisdeposit =
IF (
HASONEVALUE ( Deposits[Deposits] ),
AVERAGE ( Deposits[DepositOrder] ),
MAX ( Deposits[DepositOrder] )
)
VAR thisdepositamount =
SUM ( Deposits[Amount] )
VAR previousdeposits =
CALCULATE (
SUM ( Deposits[Amount] ),
ALL ( Deposits ),
Deposits[DepositOrder] < thisdeposit
)
VAR remainingamount = totalspend - previousdeposits
RETURN
IF (
HASONEVALUE ( Deposits[Deposits] ),
IF ( remainingamount > 0, thisdepositamount - remainingamount, 0 ),
SUM ( Deposits[Amount] ) - totalspend
)
I used different data so the #s don't match yours, but here is what it looks like in use.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- dw700d6 years agoPost Patron
mahoneypat thank you.I created the measure
I then tried to drop the measure into the visual but got the message below. Any thoughts what went wrong?
- mahoneypat6 years agoMicrosoft Employee
Make sure all your columns with numbers have a numeric data type. Also, if not already, you will need to replace the table and column names to the actual ones in your model.
Regards,
Pat
- dw700d6 years agoPost Patron
mahoneypatthanks that helped but I am still experiencing issues and its probably because I didnt explain my need well enough sorry about that .
Using your data I drop the measure in the gauge visual and it shows -8k when filtered by Deposit 1. but it should show 10k. It shows -800 when filtered by deposit 2 but it should show 7200 and it shows 8700 when filtered by deposit 3 but it should only show 800. Below is a snapshot of what I described
I want to look at the gauge on a any given day and see how much of the deposit has been spent so for example if I looked at the gauge on january 1, I would see 6,000 spent from deposit 1, 0 for deposit 2 and 0 for deposit 3. I would also like my target to adjust based on the the deposit type, so the target for deposit 1 would be 10,000, the total for deposit 2 would be 7200 and the target for depost 3 would be 9500 .Apologies for not being clear