Forum Discussion

cmcgo3's avatar
cmcgo3
Helper II
1 year ago
Solved

Filtering Issue - Hiearchy ?

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 t...
  • DataInsights's avatar
    1 year ago

    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: