Use slicer value to create calculated field

[sachin187]

Hi,

What i am trying to do is that i have stats for my sales staff on daily activity. on excel they can select the number of days they worked in a month and a formulae behind the scenes calculates the % of target they have done. For Power BI, i want to select the working days from a Slicer, the slicer is from a table i created with numbers 0-30. Then depending what has been selected, this number is used to get the for e.g. number of calls that sales person has made. 

For e.g.

(CallsTotal * 20/(Days chosen by Slicer)) /160- Calls total comes from a table, 160 is the target, 20 is number of days in a month. 

How can i create this formulae using the selection from the Slicer??

thanks

sachin 

[v-juanli-msft · Accepted solution]

Hi @sachin187

Assume "CallsTotal" and "target" are columns

To make values change with the selection of a slicer, you need create a measure

Then create a table(Table2) with numbers 0-30 disconnected with the sales staff daily activity table(Table1). (do not create any relationship with them)

Next, add Table2[days] in a slicer, create measures in the Table1

selected value = SELECTEDVALUE(Table2[days])

% of target = (MAX([calls total])*[selected value])/MAX([target])-MAX([calls total])

If "CallsTotal" and "target" are consistent values instead of columns, just input them as values in the formula above, like

% of target = (10*[selected value])/160)-10)

Best Regards

Maggie

[PattemManohar]

@sachin187 Assuming that you are all relationships in place, then try using "SELECTEDVALUE" function