Forum Discussion
comparing unfiltered data to filtered data
HI
I am fairly new to Power Bi so may just need to be pushed in right direction re functions.
I am doing profiling of estates by the EDI strands of the people living on the estates. I want to be able to filter either the page or whole report by a specific estate and be able to see how this filtered group compares to the overall population. I can see I can edit interactions and maybe have two tables for ethnic breakdown, e.g. one overall and one according to the filer applied but I'd prefer to have a single table like below. Any ideas what I should be doing to achieve this?
| Group | Overall population | Selected estate |
| Arab | 5% | 10% |
| Asian | 10% | 30% |
Black | 20% | 10% |
White | 55% | 30% |
Other | 10% | 20% |
4 Replies
- Greg_Deckler
Community Champion
SiobhanWendy Need sample data to be specific. But to do what you want, you can do something like this:
Selected Estate = VAR __Count = COUNTROWS('Table') VAR __CountSelected = COUNTROWS(ALLSELECTED('Table')) VAR __Result = DIVIDE( __Count, __CountSelected ) RETURN __Result Overall Population = VAR __Count = COUNTROWS('Table') VAR __CountAll = COUNTROWS(ALL('Table')) VAR __Result = DIVIDE( __Count, __CountAll ) RETURN __Result Greg_Deckler Tested and not quite. I think this is on the right track but not quite in right way. This loses the overall population % breakdown (which I want to remain fixed) and instead the table shows 1 column showing what % of overall pop the selected filter is, and column 2 showing the breakdown for that estate.
- Greg_Deckler
SiobhanWendy The ALLSELECTED should preserve outside filters while the ALL selects everything. Hard to know specifically what you want without seeing the data you are using. Please provide sample source data and expected output.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
So if my overall population is 45% white, I want it to remain at 45% for the 'overall' column regardless of applied filters, but I want the second column to show what % of the filtered population (e.g. by estate) is white.
