Forum Discussion

dw700d's avatar
dw700d
Post Patron
6 years ago

Using a filter with no relationship

I received three deposits in an account. Deposit 1 was 10M , Deposit 2 was 5M  and Deposit 3 was 4.25M.  Anytime a deposit hits the account the entire deposit must be spent within 31 months. The money must be spent in order of receipt so I can’t spend the 5 M from deposit 2 until the 10 M from deposit 1 is spent and I can’t spend the 4.25M from deposit 3 until deposit 2 is spent. I would like to create a gauge visual that allows me to view how much money has been spent. I would like to filter that gauge by deposit

For example the dataset below shows that by 7/30/20 we have spent 260,000 YTD.

The Gauge visual when filtered by Deposit 1 would reflect the actual spend of 260,000 and reflect a target of 10M (this is the entire amount of the deposit from Deposit 1)

When filtered by Deposit 2 it should reflect actual spend of 0 because Deposit 1 hasn’t been depleted yet and reflect a target of 5M (this is the entire amount of the deposit from Deposit 2)

When filtered by Deposit 3 it should reflect actual spend of 0 because Deposit 1 hasn’t been depleted yet and reflect a target of 4.25M (this is the entire amount of the deposit from Deposit 3)

 

 

How can I filter spend  based on Deposit 1 2 & 3?

Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

     

    • dw700d's avatar
      dw700d
      Post Patron
      6 years ago

      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?

       

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      dw700d - Yep, my only suggestion would be to post that data as text in a table. Otherwise we all have to hand enter data in order to experiment. Slows things down.