How to Make Slicer Manipulate Table Visualization of Measures

[Anonymous]

Hi,

I have a table that displays salesperson name, and two measures of Summed Quota and Sum Sales. The two measures are from separate tables 'Quota' and 'Sales'. I would like to add a Fiscal Period slicer to my page so that when I select each fiscal period, the sum totals of the table visualization are only from the fiscal periods that are selected.  I have added a slicer for fiscal period but it is not quite working. 

Measures

Summed Quota = sum(Quota[Quota])

Summed Sales = sum('Sales'[Sales])

Raw Data

Name	Fiscal Period	Quota	Sales
Jim	1	150	30
Jim	2	100	50
Jim	3	50	20

Table with all periods selected

Name	Sales	Quota
Jim	100	300

Table with fiscal period 1 & 2 selected (what I am trying to achieve)

Name	Sales	Quota
Jim	80	250

Let me know if this is clear or if you need any further information.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

You need to add a Fiscal Period dimension table (I presume both Quota and Sales tables have Fisal Period columns.

- Create a table with only unique Fiscal Period values in it. Either a calendar table or you could do:

dimFiscalPeriod =
SUMMARIZE(
    Qouta,
    Quota[Fiscal Period]
)

- Go into Model view and create relationships between your new dimension table (dimFiscalPeriod) to both of the Quota and Sales tables from Fiscal Period to Fiscal Period.

- dimFiscalPeriod side should be 'One', and Sales/Quota side should be 'Many'.

- Create slicer using dimFiscalPeriod Fiscal Period column

Good luck

[Anonymous]

Thank you this is very helpful and very close to what I'm looking for. Only thing is that it is only working for the quota column, the sales column is still totaling up to 100. Is this because of the configuration of the relationships in the data modeling?

Table with all periods selected

Name	Sales	Quota
Jim	100	300

Table with fiscal period 1 & 2 selected (what I am trying to achieve)

Name	Sales	Quota
Jim	80	250

Table with fiscal period 1 & 2 selected (what is currently happening)

Name	Sales	Quota
Jim	100	250

[BA_Pete]

Hi @Anonymous ,

See below how the Model view should look and the results I get with that set up:

[Anonymous]

I realized the issue is that another relationship I made within for my tables was cross filtering both ways instead of single. This has resolved my issue, thank you so much for your help! 

[v-frfei-msft]

Hi @Anonymous ,

Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.