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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

How to Make Slicer Manipulate Table Visualization of Measures

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
NameFiscal PeriodQuotaSales
Jim115030
Jim210050
Jim35020
 
Table with all periods selected
NameSalesQuota
Jim100300
 

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

NameSalesQuota
Jim80250

 

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

 
1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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
NameSalesQuota
Jim100300
 

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

NameSalesQuota
Jim80250

 

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

NameSalesQuota
Jim100250

Hi @Anonymous ,

 

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

 

PowerBI2020_Model.PNG

 

PowerBI2020_Report.PNG

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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! 

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors