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 segment, 
it should bring the result of that specific company comparing with the other companies in that segment.

 

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.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    phgatto Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • phgatto's avatar
      phgatto
      New Member
      4 years ago
      YEARCLIENTSEGMENTREVENUE
      2020Client 1Travel Agency1000
      2020Client 2Travel Agency1000
      2020Client 3Travel Agency500
      2020Client 4Tour Operator1500
      2020Client 5Tour Operator2000
      2020Client 6Tour Operator300
      2021Client 1Travel Agency1500
      2021Client 2Travel Agency2000
      2021Client 3Travel Agency800
      2021Client 4Tour Operator500
      2021Client 5Tour Operator700
      2021Client 6Tour Operator400

      In this case the total revenue per segment is:

      2020 TRAVEL AGENCY - 2,500

      2020 TOUR OPERATOR - 3,800

       

      2021 TRAVEL AGENCY - 4,300

      2021 TOUR OPERATOR - 1,600

       

      For example: I choose the Client 1 (segment Travel Agency), the variation YoY is: +50% (1,000 vs. 1,500)

      The Segment Travel Agency (excluding Client 1) variated: +87% (1,500 vs 2,800).

       

      If I choose Client 5 (segment Tour Operator), the variation is: -65% (700 vs 2,000)

      The Segment Tour Operator (excluding Client 5) variated: -50% (1,800 vs 900).

       

      I would like to use Bar charts to compare the variation of a specific client versus the segment its is part of.

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        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.