Filter value totals based on slider or option selections

[MrWLJ71]

Firstly I must point out I am an absolute newbie when it comes to Power BI, so please be patient!

I am so new in fact I hardly know how to ask the question for what it is I am wanting to achieve, so let me try and explain.

I have a table with a lot of columns in it, but the significant columns are as follows:

Replacement Year - a range of dates for when I need to change an item

Replacement Cost - a range of values showing how much something costs to replace

Importance Rank - Range from 1 - 27 based on lots of things but 27 is the most important and 1 is the least.

My question/need is this :

Is there a way for me to set a budget amount that will then filter my data and only show the items I can afford, based on their importance rank and replacement year?

Ideally, I would like a slider (or parameter entry box?) that will allow me to choose the maximum spend I can afford compared to the total replacement budget, then have a table to display the items, ranked 27-1, and considering the year I need to replace it.

For example, let's say I look ahead at the next 5 years and I need to spend my budget based on the importance rank (27 first then descending) and also the year it needs replacing.

Is this possible?

Thank you in advance, I would be extremely grateful for any pointers or guides. 

[MrWLJ71]

As ever amine, thank you

Now we are closer than ever!

I can see we have the running total and that is good, thank you.

When I sort by the Rank though the running total is obviously now not in the right order as it is based on the idLCAM column.

I changed idLCAM to STRATrank (twice) in the last line of code but I the get a blank display in the table. Is this because there are duplicates in the STRATrank column? (Remember 27 is high priority and the starting point and 1 is low)

It feels like there has to be a unique ranking measure based on firstly the STRATrank and then say the idLCAM... which we can then use in the last line of the code?

Is that possible?

Nearly there!

[aj1973]

The answer is simply no, the same measure can't change dynamically when you change the field filter. However you can add a new measure to the table

And I would recommend that you create a new page with the new measure and new parameter, it would make more sens

here is the formula

Est Replace Cost running total in StartRank =

CALCULATE(

    SUM('tblLCAM'[Est Replace Cost]),

    FILTER(ALL(tblLCAM),

        ISONORAFTER('tblLCAM'[STRATrank], MAX('tblLCAM'[STRATrank]), DESC)

    )

)

[MrWLJ71]

Hi Amine

I really dont follow that. I created a new page as you suggested. The table is not displaying what I expected.

[MrWLJ71]

Thank you again Amine. I have used your guidance to create the table correctly as per what I needed in the first instance, so thank you very much. 

Before I mark the solution, knowing that I cannot have a dynamic table in the way I would like, what code do I need to enter into the measure (as a new measure) for each site, so I can replicate the table on a separate page per site?

Thank you

[aj1973 · Accepted solution]

Here you go

as you can see the totals looks accurate now

https://drive.google.com/file/d/1HFjeutnPMAhojiQ6sM7gg2DOuSnJd40i/view?usp=sharing

Don't know the private message is not functioning

[MrWLJ71]

Amine

Thank you, from the bottom of my heart I truly appreciate a) your time, b)your effort, c)your patience and d)your kindness in assisting me with this! We got there !!!

I have marked this as resolved.

I am very grateful

[aj1973]

Welcome

[aj1973]

Private

[MrWLJ71]

Hi Armine. Thank you for this. I am travelling at the moment and haven't had chance to look at it. I wanted you to know I am not ignoring your response, sorry!

[aj1973]

Indeed, I did count those 2000 words lol.

Ok I ll work on it. 

[MrWLJ71]

Hi there

Thank you once again for taking the time to respond. 

The slider, which shows the budget amount is good, but it does not filter the table to only include the top ranking items which are <= the slider selection.

I thought about this a little more last night and perhaps I could attempt to explain another way.

Imagine you have some work which is necessary on your car. There table is a list of items you need or want to do. 

There are some items which MUST be done, such as new brakes and tyres etc and other things are NICE to have, such as new car mats or an air freshener for example.

IF the whole list added up to $1,000 but I only have $500 then I set the slider (budget amount) to 500$ and the table will start at the highest ranking item first and working down the list until my 500$ is spent, anything beyond that value will not be displayed in the table.

I hope that explains my output desire a little better?

Thank you once again, much appreciated.

[aj1973]

Hi @MrWLJ71 

Welcome to the world of Power bi, thus you should start here

How to Get Your Question Answered Quickly - Microsoft Power BI Community