Forum Discussion

phgatto's avatar
phgatto
New Member
4 years ago

Compare a single client performance versus a the segment performance

Hello Everyone,
I would like to find the best way to compare a client performance versus a particular segment it is part of. 
For example: When consulting a specific tour operator, it should bring the result of that specific company 
comparing with the other companies in that segment.
As the information is divided into different tables, I will share the way the data is structured:

 

First sheet:

MonthHotelClient CodeRevenue
jan/19Hotel XYZ4353249
jan/19Hotel XYZ38512825982
jan/19Hotel XYZ15941769355
feb/19Hotel XYZ435759
feb/19Hotel XYZ38512842165
feb/19Hotel XYZ1594176935
mar/19Hotel XYZ4350
mar/19Hotel XYZ38512828037
mar/19Hotel XYZ1594176955
apr/19Hotel XYZ4351001
apr/19Hotel XYZ38512832867
apr/19Hotel XYZ1594176355
may/19Hotel XYZ4352521
may/19Hotel XYZ38512870034
may/19Hotel XYZ159417610355
jan/21Hotel XYZ4350
jan/21Hotel XYZ38512856707
jan/21 Hotel XYZ159417629355
feb/21Hotel XYZ435 886
feb/21Hotel XYZ38512849385
feb/21Hotel XYZ1594176896
mar/21Hotel XYZ4355684
mar/21Hotel XYZ3851284364
mar/21 Hotel XYZ159417627244
apr/21Hotel XYZ43512972
apr/21Hotel XYZ3851287633
apr/21Hotel XYZ1594176244
may/21Hotel XYZ4357244
may/21Hotel XYZ3851289323
may/21Hotel XYZ1594176724

 

Second Sheet:

Client CodeClient NameClient GroupSegmentCountry
1594176John TourJohn TourTour OperatorBrazil
1595257John TourJohn TourTour OperatorUSA
274988John TourJohn TourTour OperatorSpain
435Mary TourMary TourTour OperatorUK
385128Terry TourTerry TourTour OperatorBrazil

 

The idea is compare a single client, or the group he belongs to, versus the segment he belongs to, for example: 
Filtering by the single client John Tour (Brazil), it compares the performance of that client over the months 
against all others in the segment.
If I filter the John Tour Group (Brazil, USA and Spain) it compares the performance of this group versus
the other clients not selected from the tour operator segment.
The idea is to insert a bar graph like the one below.

 

 

I hope I've been more efficient in my explanation now. Thanks again.

Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    phgatto , Measure

    Client rev = sum(Table[Revenue] )

    Client rev Segment= calculate(sum(Table[Revenue] ), filter(allselected(Table), Table[Segment] = max(Table[Segment]) ) )

     

    % = divide([Client rev], [Client rev Segment] )

    or

     

    % = divide([Client rev], [Client rev Segment]- [Client rev] )