I have a page with 4 filters. Year, Period, Category and Customer.
When I filter by a specific category, my customer filter results are limited to just the selected category so total categories is 100 and customers is 100,000. If I select 1 category my customer goes from 100 to 10 (expected). The first table shows count of customers and sales $ when filtered this is 10, customers $20,000 in sales. I want a second table that shows for those 10 customers, what are the sales for all of the 100 categories which I would expect to be customer count 10 and sales for those 10 customers of all categories $50,000 dollars since taking in account all 100 categories.
My issue is I can't get just the 10 customers, it does all 100 customers. Any thoughts why it doesn't keep the customer filter? If i go and indivisually select each customer then it filters by that, but if I leave blank even though the filter shows 10 customers, it does all 100 customers.
Solved! Go to Solution.
Got it! It was the filter for the Sales not being blank. Seems like that should not be necessary I will have to noodle on that for a while. Since I had a measure already I ended up using this approch rather than using the visual filters (I always forget about them later and waste time figuring out why its not working.
Revenue for All Categories = IF([Revenue],CALCULATE([Revenue],ALLEXCEPT(FACT_SALES,FACT_SALES[CustomerId])))
Though I think if I was doing this on my own report I'd use my ALTERNATE as this does not require editing interactions either.
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)
Check your relationship and cross filter direction . Make sure the filter direction is from Category to Customer in order to make sure teh filter flows.
Its hard to tell exactly what is going on without some representative data, any measures your using etc.
If the masure your using in Table 1 is
Revenue = SUM(Fact[Sales Amount])
Then the mesure you want to use in table 2 is
Revenue All Categories = CALCULATE([Revenue],ALL(Fact[Category]))
Note in some situations where you are slicing directly on your FACT table things will not work so I generally create lookup tables that I relate to the FACT table and then slice on those. Using Category as an example you can use the Create Table button from the modeling tab to create a list of your Categories automatically with this DAX
Categories = VALUES(Fact[Categories])
You should then set up your slicers on this table (after you relate it to your fact table of course) and then the measure would be
Revenue All Categories = CALCULATE([Revenue],ALL(Categories[Category]))
Here are the two images below with what is happening and the desired behavior. it seems like even though the customer dropdown slicer is filtered to just the 10. if select all is checked or nothing is checked it does all 48274 customers rather than just the 10 in the dropdown slicer. Hopefully this makes sense
Any chance you can share the power bi file?
i created the scenario using dummy data, how do I share a .zip file or power bi file on here?