Forum Discussion
Calculate measure based on Filter selection
Hi,
I would like to create a measure that is based on filter selection.
Here's an example:
I am using two filters – ‘Area – South’ filter is for ‘SOUTH’ visual(edited filter interaction)
‘Area – North’ filter is for ‘NORTH’ visual.
Problem: I am using measures for location count & sum of male & female.
The measure doesn’t work when I select the filters (see image) .
I want the measure to calculate according to the filter selection. In the below scenario I want the measure to show LocationCount =2 , SumMale = 94 , SumFemale = 67.
I have attached the sample pbix as well . Any help would be really appreciated. Thanks!
https://drive.google.com/open?id=1iEgsHFQkFx9x_xtMHpYp6E_KWISGejaM
6 Replies
My suggestion would be to create two calculated tables from your DataTable representing North and South locations.
I named them 'North Filter' and 'South Filter'.
You can achieve this with:
North Filter = all('DataTable'[LocationId];'DataTable'[Location])
South Filter = all('DataTable'[LocationId];'DataTable'[Location])
I would than create for each calculated table, one inactive relationships with DataTable.
Calculated tables to be used as filters.
After that create the following measures:
Location Count= COUNTROWS ( SUMMARIZE ( UNION ( 'North Filter', 'South Filter' ), [LocationId] ) )This will give you all the distinct locations selected.
You can create also:
North Count=
CALCULATE ( COUNTROWS ( 'DataTable' ), USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] ) )This measure will count only the locations selected on the North Filter. I'm assuming that when nothing is selected the user wants ALL.
If you setup a table visual whith a filter on this last measure for only values greater than zero, you'll get the North information.
The same thing should be done for South.
Other measures created are:
North Female= CALCULATE ( SUM ( 'DataTable'[Female] ), USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] ) ) North Male= CALCULATE ( SUM ( 'DataTable'[Male] ), USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] ) ) South Female CALCULATE ( SUM ( 'DataTable'[Female] ), USERELATIONSHIP ( 'DataTable'[LocationId], 'South Filter'[LocationId] ) ) South Male= CALCULATE ( SUM ( 'DataTable'[Male] ), USERELATIONSHIP ( 'DataTable'[LocationId], 'South Filter'[LocationId] ) )Finaly, measures for the Totals are created:
Total Female = [North Female] + [South Female] Total Male = [North Male] + [South Male]
Final Result
Here is a link to the changes necessary for my suggestion: Sample Pbix
It's due to South visuals interacting and filtering North visuals and vice versa. See below link to fix
https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions
Hi Anonymous I am having the same problem even after changing the North & South visual interaction.
Ah ok I understand what you're tryig to do now. Simply put its not possible the way you're trying to do it.
There are 2 slicers but both refer to the same column. Thats why a count can't be created when you have 2 different selections from 2 different slicers on the same column. You'd be better off doing something like this
