Show Like Records to Selection

[tb_mbisams_ap]

I need a report where user will select a store and show the metrics for the other stores in that region in a separate visual.

Table:  Store_Alignment

Store #	Region
1	South
2	South
3	East
4	West
5	East
6	South

Table:  Monthly_Sales

Store #	Month	Sales
1	Feb	$1,000
2	Feb	$1,200
3	Feb	$2,100
4	Feb	$1,700
5	Feb	$900
6	Feb	$800
1	Mar	$1,100
2	Mar	$900
3	Mar	$1,000
4	Mar	$800
5	Mar	$750
6	Mar	$1,200

The example store is #2 which is in the South.  The expected results should be the total sales for all other stores in the South like below:

Store #	Region	Sales
1	South	$2,100
6	South	$2,000
Total		$4,100

[Anonymous · Accepted solution]

Hi @tb_mbisams_ap ,

Consider creating a table with no joins.

then please create a new measure:

Measure = 
VAR __store =
    SELECTEDVALUE ( 'Table'[Store #] )
VAR __region =
    SELECTEDVALUE ( 'Table'[Region] )
VAR __table =
    CALCULATETABLE(
        VALUES ( 'Table'[Store #] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Region] = __region
                && 'Table'[Store #] <> __store
        )
    )
VAR __filter =
    IF ( MAX ( 'Monthly_Sales'[Store #] ) IN __table, 1 )
RETURN
    __filter

use the measure as the table visual's filter:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[some_bih]

Hi @tb_mbisams_ap then you need simple measure Sales=SUM(Monthly_Sales[Sales]).

In visual table, include Store column and this measure. Share feedback.

[some_bih]

Hi @tb_mbisams_ap just create relationship between two table, by Store column

[tb_mbisams_ap]

I don't think I explained enough.  These tables already have a relationship, there are other tables with other store metrics as well.  The user enters selects their store number at the top and sees various visuals around metrics exclusive to their store and then has a another visual that shows the sales for other stores in their region.