What can I afford with my budget?

[MatiasM]

Hi all,

I'm trying to analyze what funding can I afford based on budget filter.

I'll show the example below.

I do have different regions and I want to create a filter where, If I select a budget of "$35,000" it will filter the table to the regions I can afford (as a SUM), in this case it would be Mid Cantebury($12,000) and Ruapehu Whanganui ($20,000). If I select a budget of "$65,000" it will add Bay of Plenty ($32,142.86) with the regions noted before.
This is a plain table with not relationships in the Model View.

I hope you can help me, please let me know if you require more information.

Thanks

[parry2k]

@MatiasM are you sorting the region column by any other column? Can you put a sample pbix file and share using one drive/google drive?

[MatiasM]

Yeap,

[parry2k]

@MatiasM it assume that you are using only one table, can you share dax expression you used?

[MatiasM]

Hi parry2k,

Have you got any thoughts about this? If not is fine I can try something else.

Regards

[MatiasM]

Sum Funding = SUM(Regions[Funding])

Sum Funding Afford =

VAR __Table =

ADDCOLUMNS (

    ALL ( Regions ),

    "@Sum",

    CALCULATE (

        [Sum Funding],

        WINDOW (

            1, ABS,

            1, REL,

            ALL ( Regions[Region ] ),

            ORDERBY ( [Sum Funding], ASC )

        )

    ),

    "@Limit", [Parameter Value]

)

RETURN

CALCULATE (

    [Sum Funding],

    KEEPFILTERS ( FILTER ( __Table, [@Sum] <= [@Limit]  ) )

)

[parry2k]

@MatiasM create a what-if parameter for the value selection, read here how to do this,  Use parameters to visualize variables - Power BI | Microsoft Learn

and then add the following measures and use Sum Funding Afford in the visual:

Sum Funding = SUM ( 'Table'[Funding] )

Sum Funding Afford = 
VAR __Table = 
ADDCOLUMNS ( 
    ALL ( 'Table' ),
    "@Sum", 
    CALCULATE ( 
        [Sum Funding],
        WINDOW (
            1, ABS,
            1, REL,
            ALL ( 'Table'[Region] ),
            ORDERBY ( [Sum Funding], ASC )
        )
    ),
    "@Limit", [Parameter Value]
)
RETURN
CALCULATE ( 
    [Sum Funding],
    KEEPFILTERS ( FILTER ( __Table, [@Sum] <= [@Limit]  ) )
)

[MatiasM]

HI @parry2k ,Thanks for this.

I might be doing something wrong? Because the parameter is showing all the values that are under 50000. Instead of what could sum up within the budget of 50000.

Thanks again