cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables


@mnayar wrote:

@justinsweet link doesnt work


can you try again? @mnayar

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables


@mnayar wrote:

@justinsweet link doesnt work


https://drive.google.com/drive/folders/1fMCDwFkoVe8cx4ljr8w0fUZyq0ihVNBo?usp=sharing   i left off a piece of it org.  this should be good

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@justinsweet This seems expected behavior.

 

I will try to explain this better. So when you select the category slicer the customer slicer get filtered (expected) and nothing from sales table gets filtered as the interaction is removed(expected). so by default it will show all data for the selected time period.

 

you only see 2 customers 12 and 13 in there and none are selected so no interaction is happening by default it is using all customers, since nothing is selected hence it is unfiltered state.

 

If you are looking for total sales of customers filtered like 12 & 13. Renable the interaction with product and calculate the sales field so it is not impacted by product i.e. using all except. If you want other columns you can include those as well.

 

ALL EXCEPT USAGE

 

SumSales = CALCULATE(SUM(FACT_SALES[Sale]), ALLEXCEPT(DIM_CUSTOMER,DIM_CUSTOMER[CustomerId]))

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables

yeah maybe 


@mnayar wrote:

@justinsweet This seems expected behavior.

 

I will try to explain this better. So when you select the category slicer the customer slicer get filtered (expected) and nothing from sales table gets filtered as the interaction is removed(expected). so by default it will show all data for the selected time period.

 

you only see 2 customers 12 and 13 in there and none are selected so no interaction is happening by default it is using all customers, since nothing is selected hence it is unfiltered state.

 

If you are looking for total sales of customers filtered like 12 & 13. Renable the interaction with product and calculate the sales field so it is not impacted by product i.e. using all except. If you want other columns you can include those as well.

 

ALL EXCEPT USAGE

 

 

SumSales = CALCULATE(SUM(FACT_SALES[Sale]), ALLEXCEPT(DIM_CUSTOMER,DIM_CUSTOMER[CustomerId]))


Yeah, maybe you can help a little explain.  this I am not sure still helps the desire behavior cause it will be filtering the number still on the selected category.  what I want in the at 2nd grid is the all sales for all categories (expect the one selected) but only for the 2 customers 12/13.  Does that make sense.  its really to analyize these customers that bought this product vs what else do these 2 customers by 

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

create a table with 2 columns

 

1st col is just sum of sales based on filter

2nd cold is sum of sales using the formula i told you. this folmula will calculate all sales for those filtered people by the product filter.

 

Partial vs All Sales.PNG

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables


@mnayar wrote:

create a table with 2 columns

 

1st col is just sum of sales based on filter

2nd cold is sum of sales using the formula i told you. this folmula will calculate all sales for those filtered people by the product filter.

 

Partial vs All Sales.PNG


 

thanks, getting closer I think but 1 issue I am still having.  This is doing the sum of all products but it still doesn't solve the customer issue.  the exmaple above works cause the filter has all the customers. lets say in the example you provided customer 12 didn't have a sale for Product 1 and only had sales for product 2, only customers 1 and 13.  i would want the grid to show what it does now, just w/out customer 12. i only want to see the total sales for customers 1 and 13.  

 

 

example1.png

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@justinsweet actually this works for that as well, here is a screenshot. 

 

Challenge -Partial vs full sales.PNG

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables


@mnayar wrote:

@justinsweet actually this works for that as well, here is a screenshot. 

 

Challenge -Partial vs full sales.PNG


 

hmm.  well the screen shot appears to look good.  I can not get the same behavior.  any chance you can take a quick look at the report i pushed and see if you can do the same calculated measure and send back to me?  

Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

@mnayar @mnayar the screenshot does not appear to solve the problem as the resultes show mulitple cusotmers and products and I could not get the allexcept to work either.

 

@justinsweet try this solution

 

Revenue for All Categories ALT = VAR Selected_Customers = VALUES(FACT_SALES[CustomerId]) RETURN CALCULATE([Revenue],ALL(DIM_CATEGORY[CategoryName]), FACT_SALES[CustomerId] in Selected_Customers)

 

  • Selected Customers gets list of customers included in the fitlers
  • the ALL statement removes the filters for Category
  • FACT_SALES[CustomerID] in Selected_Customers filters for selected customers.

Here is a link to PowerBI - one thing that is odd is I could not figure out what how your second table was showing all customers. How did you remove the filter context on that one?

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJocyRDRZ8OZBjsGkA

 

capture20180810024228573.png 

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@Seward12533 Hello, in the screenshot, left side is all unfiltered data and right side is the result. The filtering is done by the Product Category Filter and the ALL Except is used to calculate the sum of sales for customers no matter what filter is applied.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 400 members 3,302 guests
Please welcome our newest community members: