Show a grouped table with the selected value but also include the other values in the group

[Anonymous]

Hi. I'll try to explain what I'm trying to accomplish using the below sample data.

Sales Table

Account Manager	Region	Sales
May	East	$63,139
Bobby	East	$99,216
Jasmine	Central	$38,626
Joe	West	$25,865
Tony	Central	$92,164
Larry	East	$19,038
Rosanne	West	$47,596
Amanda	West	$42,563

Sales Table is the main table. I have a separate Region table as a lookup.

If I select Jasmine in a filter to see her sales, I want to have a table that shows all of the Central region (because she is under that region) and list out all the account managers. This will allow me to see how she did in that region against the other managers. The end result should look like this sorted by sales...

Central
	Tony	$92,164
	Jasmine	$38,626

It would be even better if I can have her name highlighted. Thank you!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

According to your description, here’s my solution

1.Create a new table for the slicer of account manager, there is no relationship between the two tables, in this way, the slicer will not directly filter the account manager in the sales table.

Table = VALUES('Sales'[Account Manager])

2.Create a measure, and put it into the visual filter, then set show items when the value is 1.

Check =
IF (
    MAX ( 'Sales'[Region] )
       = MAXX (
            FILTER (
                ALL ( 'Sales' ),
                'Sales'[Account Manager] = SELECTEDVALUE ( 'Table'[Account Manager] )
            ),
            'Sales'[Region]
        ),
    1,
    0
)

3.Create another measure for the highlight, then open conditional formatting to apply the color.

Color = IF(MAX('Sales'[Account Manager])=SELECTEDVALUE('Table'[Account Manager]),"Red")

I attach my sample bellow for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! This will work perfectly!

[Ashish_Mathur]

Hi,

See if my solution here helps - Filter a column of a Pivot Table on a certain condition but also show other items from that column.

[Nathaniel_C]

Hi @Anonymous ,

When you say "If I select Jasmine in a filter to see her sales" , what do you mean by in a filter?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel