cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerdude
New Member

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

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

 

Sales Table

Account ManagerRegionSales
MayEast$63,139
BobbyEast$99,216
JasmineCentral$38,626
JoeWest$25,865
TonyCentral$92,164
LarryEast$19,038
RosanneWest$47,596
AmandaWest$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!

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

Hi @powerdude ,

According to your description, heres 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])

vkalyjmsft_0-1638438825509.png

 

vkalyjmsft_1-1638438825514.png

 

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
)

vkalyjmsft_6-1638438857993.png

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")

vkalyjmsft_3-1638438825517.png

 

vkalyjmsft_4-1638438825518.png

 

vkalyjmsft_5-1638438825519.png

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.

View solution in original post

4 REPLIES 4
v-kalyj-msft
Community Support
Community Support

Hi @powerdude ,

According to your description, heres 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])

vkalyjmsft_0-1638438825509.png

 

vkalyjmsft_1-1638438825514.png

 

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
)

vkalyjmsft_6-1638438857993.png

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")

vkalyjmsft_3-1638438825517.png

 

vkalyjmsft_4-1638438825518.png

 

vkalyjmsft_5-1638438825519.png

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.

View solution in original post

Thank you! This will work perfectly!

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nathaniel_C
Super User
Super User

Hi @powerdude ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors