I need to change value with help of a slicer

[nor303]

Hi

I want to calculate a new value of LTV with help of a Slicer in percent. I tried to make the slicer as a table. Ive tried to do this several of ways but none seems to work. Any good ideas on how to do this?

Here is how I think it should look like;

Customer	Loan	Value	LTV	Value changed with a slicer in %	LTV after change in value
1	10	25	0,4	=value*1-slicer value	=Loan/Value changed with a slicer in%
2	25	30	0,833333	=value*1-slicer value	=Loan/Value changed with a slicer in%
3	20	18	1,111111	=value*1-slicer value	=Loan/Value changed with a slicer in%
4	50	78	0,641026	=value*1-slicer value	=Loan/Value changed with a slicer in%
5	12	10	1,2	=value*1-slicer value	=Loan/Value changed with a slicer in%

However if its possible to make this in a cleaner way that would be great. I have not used power query since Im not so familiar with that yet, but this would be a repetitive task so I guess it would be great to "automate" this if possible. 

THanks:)

[punitkhatri · Accepted solution]

Hey @nor303 , For this you'll have to make a seperate table of slicer values and this table should not be related to any other table in your data model. After this you'll need the following measures.

-> 

LTV = DIVIDE(SUM('Slicer Change'[Loan]), SUM('Slicer Change'[Value]))

->

Value Change With Slicer = SUM('Slicer Change'[Value]) * (1 - SELECTEDVALUE('Slicer Values'[Slicer Value]))

->

LTV Changed = DIVIDE(SUM('Slicer Change'[Loan]), [Value Change With Slicer])

This is the dataset that I've used.

And this is the output that I've got :-

Try this and let me know if this works or not.

Happy to help.

[nor303]

Hi again, I have a follow up on this. Is it possible to group the LTV changed in intervalls? And after that present the Loan in the different intervalls? And finally if I chose more than one slicer value, can I get the different results shown side by side in a clustered column chart? That would have been awesome!

[nor303]

Hi again

This seems to work, thank you! But can I easy use these measures over several of sheets? I will have new data where I would like to do this each month in a effective way.

[punitkhatri]

Yes you can.

[nor303]

Ok thats good to hear, how can I do this? Sorry for all the questions:)

[nor303]

Hi 

Thank you! Seems like the solutions Im looking for.

Im sorry, but Im totally new to this. I make this measures in the "Report view" section? Not a new column in the data view section? 

[punitkhatri]

You'll have to make the measures in the report view.

[DataVitalizer]

Hi @nor303 

You can create a column with numerical values which will represent the percent, whenever you select a value it will be reflected in your calculated measure.

The steps are available here.

Did it work ?  ‌‌ Mark it as a solution to help spreaing knowledge
Was this exchange helpful ? A kudos 👍 would be appreciated

[nor303]

Hi, thanks:) But I got an error trying to open the link. 404 to be exact.

[nor303]

And to be more specific; the slicer Ive made looks like this in a table:

LTV endring	% change
	-1,00
	-0,75
	-0,50
	-0,30
	-0,25
	-0,20
	-0,15
	-0,10
	-0,05
	0,00
	0,05
	0,10
	0,15
	0,20
	0,25
	0,30
	0,50
	0,75
	1,00

[DataVitalizer]

My bad here is the full link https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if 

Make sure to create the field as below to get decimal numbers

Did it work ?  ‌‌ Mark it as a solution to help spreading knowledge
Was this exchange helpful? A kudos 👍 would be appreciated

[DataVitalizer]

@nor303  Any update?

Did it work ?  ‌‌ Mark it as a solution to help spreading knowledge
Was this exchange helpful? A kudos 👍 would be appreciated