Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matheus_bnm
New Member

Filter as a reference

Hi everyone.

 

I'm working on a Sales Table like this one below:

 

   DATE     PRODUCT ID    Revenue
1/1/2015         AA               100
2/2/2015         AB               200
3/3/2015         AA               300
4/4/2015         BA               400
5/5/2015         BB               500

 

It is easy to plot Revenue vs. Date, no problems so far.

 

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., I want to let the the user choose one of the dates (01/01, 02/02/ 03/03,... ) as a Reference for the Revenue Column.

 

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.

 

   DATE      PRODUCT ID    Revenue [NEW]
1/1/2015         AA               0.5 (i.e., 100/200)
2/2/2015         AB               1.0 (i.e., 200/200)
3/3/2015         AA               1.5 (i.e., 300/200)
4/4/2015         BA               2.0 (i.e., 400/200)
5/5/2015         BB               2.5 (i.e., 500/200)

 

And then, finnaly, I want a Graph Date vs. Revenue [New], using these new values (0.5, 1.0, 1.5 ...).

 

Does anyone know how to solve it?

 

It seems do be quite easy, but I don't get the answer.

 

Regards,

Matheus.

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Matheus_bnm,

 

In your scenario, you can create another table which has the DATE and Revenue columns. Make sure there is no relationship between this new table and the original one.

 

Table = SELECTCOLUMNS('Fact',"Date",'Fact'[Date],"Revenue",'Fact'[Revenue])

 

Then create a measure in this new table:

SelectValue = IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Revenue]),BLANK())

 

Create a measure in original table:

Revenue(NEW) = DIVIDE(SUM(Fact[Revenue]),'Table'[SelectValue],0)

 

Then drag the Date column from the new table into a slicer visual,

 

q1.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-qiuyu-msft
Community Support
Community Support

Hi @Matheus_bnm,

 

In your scenario, you can create another table which has the DATE and Revenue columns. Make sure there is no relationship between this new table and the original one.

 

Table = SELECTCOLUMNS('Fact',"Date",'Fact'[Date],"Revenue",'Fact'[Revenue])

 

Then create a measure in this new table:

SelectValue = IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Revenue]),BLANK())

 

Create a measure in original table:

Revenue(NEW) = DIVIDE(SUM(Fact[Revenue]),'Table'[SelectValue],0)

 

Then drag the Date column from the new table into a slicer visual,

 

q1.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.