Distributing Value into specified category

[KrikkitWars42]

I have the following dataset and a seperate total amount that varies, let's say at this time it is -150. I need to distribute that -150 to specific categories untill -150 is at 0. For example the -150 will first go to Dog - 30, making Dog - 0, and then we have -120 that will go into Groceries - 400, making groceries 280, and that takes up all of the original -150 so it doesnt need to be distributed further. 

The way I am currently doing it is causing memory issues, so I am hoping you all know of better way to do it. 

Thanks!

[v-polly-msft · Accepted solution]

Hi @KrikkitWars42 ,

I have created a  simple sample, please refer to it to see if it helps you.

Create measures.

Measure =
VAR _2 =
    IF ( MAX ( 'Table'[Index] ) >= 4, [Budget by categorym], BLANK () )
RETURN
    SUMX (
        FILTER ( ( 'Table' ), 'Table'[Index] >= SELECTEDVALUE ( 'Table'[Index] ) ),
        _2
    )

Measure2 =
VAR _1 = - ( -150 )
VAR _2 =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) ),
        [Measure]
    )
VAR _re =
    IF ( _1 <= _2, MAX ( 'Table'[purchase] ), BLANK () )
RETURN
    _re

result =
VAR _1 =
    MINX ( FILTER ( ALL ( 'Table' ), [Measure 2] <> BLANK () ), 'Table'[Index] )
RETURN
    IF (
        MAX ( 'Table'[Index] ) <= _1
            && [Measure] <> BLANK (),
        MAX ( 'Table'[purchase] ),
        BLANK ()
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.