Forum Discussion

cmcgo3's avatar
cmcgo3
Icon for Helper II rankHelper 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 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 

Need Help
  • DataInsights's avatar
    DataInsights
    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:

     

     

     

4 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    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:

     

     

     

    • cmcgo3's avatar
      cmcgo3
      Icon for Helper II rankHelper II
      1 year ago

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

    • cmcgo3's avatar
      cmcgo3
      Icon for Helper II rankHelper II
      1 year ago
      • What would measure look like if you wanted average of all names excluding selected name?
      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        1 year ago

        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.