Month Select Slicer use value in calculation

[dkretzer]

Hi everyone, long time lurker, first time poster.

I have a bar chart that compares a Sales person's actual sales versus quota. Quota is figured at $60K/m.

I have a date slicer that uses the Actual Revenue Sold( "Close Date") date, and shows only the month (dont care about the particular day, only the month).

I need my chart to show the accumultive quota for a specific month. Example: January should be 60K, February 120K,...

I have tried using SELECTEDVALUE in several ways. Selectedvalue('Weekly Overview'[Close Date].[Month No])*60000 is halfway successful, but doesn't work when multiple months are selected. It will show February quota as 120K, but because this limits my revenues to just february, I need it to show: Revenues (Jan+Feb), Quota (Feb->2*60000).

Any help is greatly welcomed!

[v-jianpeng-msft · Accepted solution]

Hi, @dkretzer 

Based on your description, I have created the following sample data:

The Index column corresponds to the month number. I made a slicer and a bar chart based on your description as follows:

For multi-select cases, we need to use the seletcolumn function to configure the addcolumn function to achieve the effect you are talking about.

Measure = 
VAR _table = SELECTCOLUMNS('Table','Table'[Index])
VAR _table1 = ADDCOLUMNS(_table,"quota",'Table'[Index]*60000)
RETURN MAXX(FILTER(_table1,'Table'[Index]=SELECTEDVALUE('Table'[Index])),[quota])

The first variable _table take out multiple values selected by the slicer to form a virtual table. The second variable _table1 add a column to the _table to calculate the quota for the selected month. Finally, the quota of the quota column in the _table1 that corresponds to the current x-axis is returned.

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.