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
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.