cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Two Views of the same Data Set-

I want to see monthly Sales by new customer and total monthly sales (all customers) in the same dashboard. I have a customer status field that distinguish new customer from existing one.  I have this field to the report filter and selected new customers to get sales by new customer however by doing this i can't get my total monthly sales . Also I try to use slicer to filter the data but i got the same results i can't see sales by new customer and total sales in the same dashboard

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: Two Views of the same Data Set-

So the issue is the filters filter your data model and in doing so it can create this issue. Consider a simple example. You have a table of sales by product. You filter the table to see sales of green products, but then you no longer see total sales of all products. 

 

The solution is to write formulas that over come this issue. I suggest you write a new measure that gives sales for new customers without needing to use the filter. So the 2 measures you need will be similar to this

 

total sales = sum (dataTable[sales column])

total sales new customers = CALCULATE ([Total Sales] , table[new cust status]="new")

 

the second filter will work without the need for filtering - the calculate adds the filter. Just rename all the tables and columns to match what is your data model

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Advocate III
Advocate III

Re: Two Views of the same Data Set-

Totally agree with solution provided by orionlee.

 

Just providing higher level clarification, that for any report, there will be three types of filters:

1) Report Level Filters - You use them to filter out unwated data from your entire report, including mutliple tabs (but data remains available for other reports created and saved separetely).

2) Page Level Filters -  Use them to filter out data from entire page (or tab). So if there are multiple charts/tables visible on same page, data will be filtered from all of them.

3) Visual Level Filter - This is specific to individual elements added to chart (and so it appears only when you click on any element, like a chart or table). Use this to filter out data from that single chart or table, but the data still appears on other charts/tables within the report.

 

Hope this helps clearify the things.

 

View solution in original post

3 REPLIES 3
Highlighted

Re: Two Views of the same Data Set-

So the issue is the filters filter your data model and in doing so it can create this issue. Consider a simple example. You have a table of sales by product. You filter the table to see sales of green products, but then you no longer see total sales of all products. 

 

The solution is to write formulas that over come this issue. I suggest you write a new measure that gives sales for new customers without needing to use the filter. So the 2 measures you need will be similar to this

 

total sales = sum (dataTable[sales column])

total sales new customers = CALCULATE ([Total Sales] , table[new cust status]="new")

 

the second filter will work without the need for filtering - the calculate adds the filter. Just rename all the tables and columns to match what is your data model

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Two Views of the same Data Set-

It sounds like you want to use a visual level filter and not a report level filter (slicers are similar to page level filters). What I would recommend is this:

 

1) Clear your report filters

2) Select your visual that you want to use to show new customer sales. Set the visual filter to to new customers

3) Make sure your total customer sales visual has no filters on your new/old customer field.

 

That should allow you to have two visuals, one showing new customer sales only and the other showing all customers. Does that help solve your problem?

 

visualFilter.PNG

Highlighted
Advocate III
Advocate III

Re: Two Views of the same Data Set-

Totally agree with solution provided by orionlee.

 

Just providing higher level clarification, that for any report, there will be three types of filters:

1) Report Level Filters - You use them to filter out unwated data from your entire report, including mutliple tabs (but data remains available for other reports created and saved separetely).

2) Page Level Filters -  Use them to filter out data from entire page (or tab). So if there are multiple charts/tables visible on same page, data will be filtered from all of them.

3) Visual Level Filter - This is specific to individual elements added to chart (and so it appears only when you click on any element, like a chart or table). Use this to filter out data from that single chart or table, but the data still appears on other charts/tables within the report.

 

Hope this helps clearify the things.

 

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors