Forum Discussion

sachin187's avatar
sachin187
New Member
7 years ago
Solved

Use slicer value to create calculated field

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 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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