Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Powdawan
New Member

Comparing sales Vs group sales with slicer

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.

 

 

SalesmanRegion sales amount
   
Salesman 1A50
Salesman 2A75
Salesman 3A425
Salesman 4B12
Salesman 5B21
Salesman 6B23
Salesman 7C223
Salesman 8C14
Salesman 9D32
Salesman 10E114
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Think this is what you had in mind:

Total Based on Region.png

Total Sales = SUM ( Table1[sales amount] )

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

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Think this is what you had in mind:

Total Based on Region.png

Total Sales = SUM ( Table1[sales amount] )

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.