Filter as a reference

Question

Hi everyone.&nbsp;I'm working on a Sales Table&nbsp;like this one below:&nbsp;&nbsp;&nbsp; DATE&nbsp;&nbsp;&nbsp;&nbsp; PRODUCT ID&nbsp;&nbsp;&nbsp; Revenue1/1/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1002/2/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 2003/3/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;3004/4/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 4005/5/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;500&nbsp;It is easy to plot Revenue vs. Date, no problems so far.&nbsp;But what I really want to do is the following:I want to use a filter by date (available for user - through the Dashboard), i.e.,&nbsp;I want to let the the user&nbsp;choose one of the dates (01/01, 02/02/ 03/03,... ) as a Reference&nbsp;for the Revenue Column.&nbsp;In other words, if the user selects "02/02/2015" on a filter, the value "200" is the new reference and others values of this Column (Revenue) is now referenced as below:obs: it doesn't need to change the values of the table. I just want to use these values to plot the graph descibed below.&nbsp;&nbsp;&nbsp; DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRODUCT ID&nbsp;&nbsp;&nbsp; Revenue [NEW]1/1/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.5 (i.e., 100/200)2/2/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 1.0 (i.e., 200/200)3/3/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;1.5 (i.e., 300/200)4/4/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.0 (i.e., 400/200)5/5/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.5 (i.e., 500/200)&nbsp;And then,&nbsp;finnaly, I want a Graph Date vs. Revenue [New], using these new values (0.5, 1.0, 1.5 ...).&nbsp;Does anyone know how to solve it?&nbsp;It seems do be quite easy, but I don't get the answer.&nbsp;Regards,Matheus.

v-qiuyu-msft · Accepted Answer

Hi Matheus_bnm,
&nbsp;
In your scenario, you can create another table which has the DATE and Revenue columns. Make sure there is no relationship between this new&nbsp;table and the&nbsp;original one.
&nbsp;
Table = SELECTCOLUMNS('Fact',"Date",'Fact'[Date],"Revenue",'Fact'[Revenue])
&nbsp;
Then create a measure in this new table:
SelectValue = IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Revenue]),BLANK())
&nbsp;
Create a measure in&nbsp;original table:
Revenue(NEW) = DIVIDE(SUM(Fact[Revenue]),'Table'[SelectValue],0)
&nbsp;
Then drag the Date column from the new table into a slicer visual,
&nbsp;

&nbsp;
&nbsp;
Best Regards,Qiuyun Yu

Forum Discussion

Filter as a reference

1 Reply

Related content

data exchange use filters and add reference parameters

dynamic reference line

Azure Key Vault References

referer (referring) URL in usage stats?

Fabric reference architecture