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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
      Icon for Community Support rankCommunity 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.