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. 

[v-henryk-mstf]

Hi @MrWLJ71 ,

Does @aj1973  answer solve your confusion? If not, please point out, I will answer you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[MrWLJ71]

Hi Henry

Thank you for your note. I have just replied to Amine first, as a matter of courtesy.

We are close, but not quite there yet.

We now have the running total but the priority order begins with the STRATrank column and then can by be idLCAM. I appreciate that perhaps the STRATrank column may need a helper column (excel term I use!) or new measure to create a further unique ranking option if the duplicate values is the issue?

Thank you

[MrWLJ71]

Thank you for taking the time to reply and pointing me in the right direction. I have now (hopefully) detailed the link to an example of the pbix I have thrown together to give you an example.

Test pbix 

I hope you can gain access?

I appreciate the help

[aj1973]

Hey @MrWLJ71 

got your file, thanks.

Can you better explain your request with examples of the out come? 

How do you want to calculate the budget?

You want to compare it to what?

... 

[MrWLJ71]

OK, so now you have seen the file, perhaps what I need will make more sense.

Firstly, I have noticed that the chart across the bottom is showing average cost per year, this should be the sum, apologies.

So, for the replacement year slider, it would be great if the use could select the range of years they would like. In my example I have 2021 - 2030 and the total budget required would be 93.79M

The challenge is that if the budget was only, say 50M, then can the table be automatically filtered, based on rank, so that the necessary investment was <= 50M?

This is also dependent upon the site being chosen.

So for the same year range, but for NWP the investment is 14.37M but if the budget was only 8M then is there a way of displaying the filtered table so that, from the highest rank first, the displayed list would only show those items which could be replaced, in the year range, for that site, highest rank first, investment coming in <= 8M ?

Finally, the idea is that the text box I used (it doesn't matter what it has to be, text box, slider or whatever) can this be where the user enters the budget amount and then the above filtering goes on based on this value?

I hope that is making sense?

Thank you 

[aj1973]

Hi @MrWLJ71 

Is this what you want to see

The file is attached here.

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

[MrWLJ71]

I trust you had a good weekend?

Please let me know if you managed to get anywhere with the solution, just so I know?

Thank you for your time

[aj1973]

Hi @MrWLJ71 

Sorry, didn't look into it lately. 

still trying to understand what exactly you want to see! Like this ?

Or show an example, 1 img is better than 1000 words, you know!

[MrWLJ71]

Actually, I have managed to do something in Excel which I hope demonstrates, pictorially, what I am trying to explain with words!!!

In the first image you can see my formula is a simple sum which works out the running total.

In the second image I show the simple IF formula to include Yes or No.

In excel, what I would now normally do is filter the table based on the yes or no (true or false) result when the running total of the line item is compared to the budget value set.

I do hope this helps more explain !!!

The example excel file is here : Excel example 

Thank you 

[MrWLJ71]

Hello Amine

So sorry to bother you again. Did you manage to take another look after I posted the Excel example? Thank you

[MrWLJ71]

Hi there, thank you for the reply. i can appreciate I am not a top priority 😂

Did you see my previous response, the car example? The image you included is not what I need sorry, no.

Imagine you only had 100$ to spend on Christmas gifts. You know the price that everything costs and if you bought everything it would come to 200$, which you simply cannot afford.

So your priority maybe your Mum, Dad, Wife, Husband, Son, Daughter etc... and if all that adds up to 80$ then you can move on to the next people in the priority list until you have spent <= 100$

I have included the ranking, 27 High to 1 Low, and the replacement costs.

The 'budget' (ie the 100$) is what I need the parameter box for, so that the user can enter the total budget available. This in turn should filter the table to only include the items which can be replaced, for which the cumulative sum comes in less than or equal to the maximum budget.

Unfortunately, I don't know how to represent this in BI or I would show an image.

I was hoping my descriptions would explain it?

Is there a way to have a running sum - cumulative amount? (perhaps in another field in the table) that is filter from rank 27 first to 1 last, and if the cumulative total is <= to the maximum budget (from the users parameter box) then it is displayed, if not, it is not displayed?

[aj1973]

Well, I like your sense of humor and for this

I am going to work hard to help you out even though you are not making my life any easier...

[MrWLJ71]

Ha Ha, Thank you

It was my effort to remain respectful. I appreciate you even looking at it let alone taking the time to assist me.

I now posted two pictures, which should just about bring me up to my 2000 words I already used 🤣😂

Thank you, as ever

[aj1973]

Please check it out, I just updated the file.

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

Is this what you need

[MrWLJ71]

Sorry, but unless I am doing something wrong, this doesn't appear to work.

For example, if I set the budget amount to 30,000, even if the total projected spend is 140+M then it still shows all items and not just those I can afford in the 30,000

In the second photo, with 30K set as the budget, the table should stop displaying at row 3231 when the value is 26,640 as the next value would breach the 30K spending limit ie the budget.

[aj1973]

In my file The Budget can't be 30k. the parameter i set increase by 100k.

Do you want to see 30k then change the parameter here as you wish

like here

It's up to you how you want to set up the parameter.

[MrWLJ71]

Good afternoon and hello again!

I have now managed to take a look at the example you provide, thank you once again for making the effort.

I can now see where we are not aligned. The filter you have provided is for each line item being less than the budget amount set, eg, if the budget is 100K then anything at 50k is a yes, 101k is not a yes and therefore not displayed.

BUT, that is not what I wanted sorry. See the example from excel below.

I have run a cumulative total based on the running total of the est Replacement Cost. The budget is set in the green box.

When the CumulTotal amount breaches the budget (1,000,000 in this case) the CTfilter becomes N (No) and not Y (Yes)

Does that help?

Again, apologies for the delay between responses, I was travelling.

Kind regards

[aj1973]

Hi Will,

You just need to play with Parameter and you will get to see whatever you want!

[MrWLJ71]

Hi Amine

This isnt right though. If you look at the 'running total' column it is the same value as the est replacement cost and NOT a running total. see underlined in red.

However if you look at my excel version, you can see how the running total increases in value with the new line item cost, underlined in red

Then you can also see how the Y / N filter changes when the running total value is greater than the budget amount... see green underline

[aj1973]