Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
@Seward12533 I am waiting for IT to update my Power BI to the latest version so I can access your file. But I think I know the issue.
You have edited interactions to not allow it to be filtered by Category right????
Solution
1) First Column SUM(Sales) : This will give sales for the filtered customer for the specific category
2) Second Column: Sum(Sales) using AllExcept Formula by customer id: this will gave sales for the filtered customer no matter what category.
@Anonymous 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
Upload it to dropbox or google drive and post the link here.
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]))
provided some screen shots below. maybe this helps
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.
Thanks
Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |