Forum Discussion
Filtering Issue - Hiearchy ?
- 1 year ago
Try this solution.
1. Create a disconnected table (no relationships). This can be done in Power Query or DAX. This example uses a DAX calculated table.
Slicer Table = SUMMARIZE ( 'Table', 'Table'[Name], 'Table'[Region], 'Table'[Team] )2. Create measures.
Attempts = SUM ( 'Table'[Attempts] )Completions = SUM ( 'Table'[Completions] )Percentage = DIVIDE ( [Completions], [Attempts] )Percentage by Name = CALCULATE ( [Percentage], KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Name] ), 'Table'[Name] ) ) )Percentage by All Others in Region = VAR vAllNamesInRegion = CALCULATETABLE ( VALUES ( 'Table'[Name] ), KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Region] ), 'Table'[Region] ) ) ) VAR vOtherNamesInRegion = EXCEPT ( vAllNamesInRegion, VALUES ( 'Slicer Table'[Name] ) ) VAR vResult = CALCULATE ( [Percentage], vOtherNamesInRegion ) RETURN vResultPercentage by All Others in Team = VAR vAllNamesInTeam = CALCULATETABLE ( VALUES ( 'Table'[Name] ), KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Team] ), 'Table'[Team] ) ) ) VAR vOtherNamesInTeam = EXCEPT ( vAllNamesInTeam, VALUES ( 'Slicer Table'[Name] ) ) VAR vResult = CALCULATE ( [Percentage], vOtherNamesInTeam ) RETURN vResult3. The "Name" slicer should use 'Slicer Table'[Name].
4. Each table visual should use its corresponding measure as a filter ("is not blank").
OVERALL:
REGION:
TEAM:
Result:
Try this solution.
1. Create a disconnected table (no relationships). This can be done in Power Query or DAX. This example uses a DAX calculated table.
Slicer Table = SUMMARIZE ( 'Table', 'Table'[Name], 'Table'[Region], 'Table'[Team] )
2. Create measures.
Attempts = SUM ( 'Table'[Attempts] )Completions = SUM ( 'Table'[Completions] )Percentage = DIVIDE ( [Completions], [Attempts] )Percentage by Name =
CALCULATE (
[Percentage],
KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Name] ), 'Table'[Name] ) )
)Percentage by All Others in Region =
VAR vAllNamesInRegion =
CALCULATETABLE (
VALUES ( 'Table'[Name] ),
KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Region] ), 'Table'[Region] ) )
)
VAR vOtherNamesInRegion =
EXCEPT ( vAllNamesInRegion, VALUES ( 'Slicer Table'[Name] ) )
VAR vResult =
CALCULATE ( [Percentage], vOtherNamesInRegion )
RETURN
vResultPercentage by All Others in Team =
VAR vAllNamesInTeam =
CALCULATETABLE (
VALUES ( 'Table'[Name] ),
KEEPFILTERS ( TREATAS ( VALUES ( 'Slicer Table'[Team] ), 'Table'[Team] ) )
)
VAR vOtherNamesInTeam =
EXCEPT ( vAllNamesInTeam, VALUES ( 'Slicer Table'[Name] ) )
VAR vResult =
CALCULATE ( [Percentage], vOtherNamesInTeam )
RETURN
vResult
3. The "Name" slicer should use 'Slicer Table'[Name].
4. Each table visual should use its corresponding measure as a filter ("is not blank").
OVERALL:
REGION:
TEAM:
Result:
- What would measure look like if you wanted average of all names excluding selected name?
- DataInsights1 year agoSuper User
Glad to hear this solution works. Regarding the average, first create an average measure. Then, copy measures [Percentage by All Others in Region] and [Percentage by All Others in Team], and replace the reference to measure [Percentage] with your average measure.