Filtering Issue - Hiearchy ?

[cmcgo3]

This is probably a simple fix; but I cannot quite get it. 

Ultimately, I am trying to create visual (preferably gauge) to show "Andrew Phillips" percentage as well as separate visual to compare the others included on his "Team F", (B. Davis and J. Davis) and thirdly, a visual to compare others included in his region "Northeast", (B. Davis, Jackson, Williams, J. Davis, and Smith).

In attached file, if I click on "Andrew Phillips" who is on Team "F" and region "Northeast", the first visual is correct; percentage is 33.77%.  In the next visuals down, titled "Region", I would like to calculate the percentage of all others within that region which would be Davis, Jackson, Williams, Davis, and Smith which should calculate to 24.73%.

In the final visual, I would like to calculate the percentage of all others within that team which would be B. Davis and J Davis, which should calculate to 22.78%.

Any help would be appreciated!

https://drive.google.com/file/d/1VO9xfk8O3zH0pe7nShJVP4fP0Myi2xud/view?usp=sharing 

[DataInsights · Accepted solution]

@cmcgo3,

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
    vResult

Percentage 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:

[cmcgo3]

• What would measure look like if you wanted average of all names excluding selected name?

[DataInsights]

@cmcgo3,

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.

[cmcgo3]

This is AWESOME!!  I knew I needed a disconnected table, but wasnt sure about the rest!  Thank you !!