Comparing sales Vs group sales with slicer

[Powdawan]

Hi guys ,

I am sure you will find the solution to my problem.

I have a table as below and I want to  show the  amount for my salesman and for the region associated.

I have a slicer in the top of my  home page to choose my salesman

If I choose salesman1 I want to retrieve "automatically" the region A  associated and calculate the  total sales for this region .

In SQL thaht would be

Select sum (sales_amount) from table1  where region in (select distinct region from table 1 where salesman = 'salesman1')

The final result would be 2 KPIs

Salesman1    50        |         Region  A  550 

If I choose in the slicer salesman 2 for example 

Salesman1    75        |         Region  A  550 

If I choose in the slicer salesman 10 for example 

Salesman10    114      |          Region  E  114 

and so on ....

I tried , filter , ALL, ALLSELECTED, Etc.... but I was not successful  , please help me  🙂 

many thanks in adance.

Salesman	Region	sales amount
Salesman 1	A	50
Salesman 2	A	75
Salesman 3	A	425
Salesman 4	B	12
Salesman 5	B	21
Salesman 6	B	23
Salesman 7	C	223
Salesman 8	C	14
Salesman 9	D	32
Salesman 10	E	114

[Anonymous · Accepted solution]

Think this is what you had in mind:

Total Sales = SUM ( Table1[sales amount] )

Total Region Sales =
 CALCULATE( 
     [Total Sales], 
     FILTER( 
         ALL ( Table1 ), 
         Table1[Region ] = max( Table1[Region ])
    )
)

[Powdawan]

Hi Nick , thank you so much for the solution . I will try it and let you know !! You made my day 🙂