Forum Discussion

phgatto's avatar
phgatto
New Member
4 years ago
Solved

Compare a client performance versus a 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 company performance in the Oil and Gas se...
Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi phgatto 

    Thanks for reaching out to us.

     

    You can try this,

     

    Measure1 = 
    var _minYear=CALCULATE(MIN('Table'[YEAR]),ALLSELECTED('Table'))
    var _maxYear=CALCULATE(MAX('Table'[YEAR]),ALLSELECTED('Table'))
    var _value1=CALCULATE(MAX('Table'[REVENUE]),FILTER(ALLSELECTED('Table'),'Table'[YEAR]=_minYear))
    var _value2=CALCULATE(MAX('Table'[REVENUE]),FILTER(ALLSELECTED('Table'),'Table'[YEAR]=_maxYear))
return (_value2-_value1)/_value1
    Measure2 = 
    var _getSegment=SELECTEDVALUE('Table'[SEGMENT])
    var _getClient=SELECTEDVALUE('Table'[CLIENT])
    var _minYear=CALCULATE(MIN('Table'[YEAR]),ALLSELECTED('Table'))
    var _maxYear=CALCULATE(MAX('Table'[YEAR]),ALLSELECTED('Table'))
    var _value1=CALCULATE(SUM('Table'[REVENUE]),FILTER(ALL('Table'),'Table'[YEAR]=_minYear && 'Table'[CLIENT]<>_getClient && 'Table'[SEGMENT] =_getSegment))
    var _value2=CALCULATE(SUM('Table'[REVENUE]),FILTER(ALL('Table'),'Table'[YEAR]=_maxYear && 'Table'[CLIENT]<>_getClient && 'Table'[SEGMENT] =_getSegment))
return (_value2-_value1)/_value1

     

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

phgatto's avatar
phgatto
New Member
4 years ago
First of all, sorry for replying too long later. 
I admit that I still had difficulties in solving the problem. 
As the information is divided into different tables, I will share the way the data is structured and so, it may be simpler.

 

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.