Compare a client performance versus a segment performance

[phgatto]

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.

[phgatto]

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:

Month	Hotel	Client Code	Revenue
jan/19	Hotel XYZ	435	3249
jan/19	Hotel XYZ	385128	25982
jan/19	Hotel XYZ	1594176	9355
feb/19	Hotel XYZ	435	759
feb/19	Hotel XYZ	385128	42165
feb/19	Hotel XYZ	1594176	935
mar/19	Hotel XYZ	435	0
mar/19	Hotel XYZ	385128	28037
mar/19	Hotel XYZ	1594176	955
apr/19	Hotel XYZ	435	1001
apr/19	Hotel XYZ	385128	32867
apr/19	Hotel XYZ	1594176	355
may/19	Hotel XYZ	435	2521
may/19	Hotel XYZ	385128	70034
may/19	Hotel XYZ	1594176	10355
jan/21	Hotel XYZ	435	0
jan/21	Hotel XYZ	385128	56707
jan/21	Hotel XYZ	1594176	29355
feb/21	Hotel XYZ	435	886
feb/21	Hotel XYZ	385128	49385
feb/21	Hotel XYZ	1594176	896
mar/21	Hotel XYZ	435	5684
mar/21	Hotel XYZ	385128	4364
mar/21	Hotel XYZ	1594176	27244
apr/21	Hotel XYZ	435	12972
apr/21	Hotel XYZ	385128	7633
apr/21	Hotel XYZ	1594176	244
may/21	Hotel XYZ	435	7244
may/21	Hotel XYZ	385128	9323
may/21	Hotel XYZ	1594176	724

Second Sheet:

Client Code	Client Name	Client Group	Segment	Country
1594176	John Tour	John Tour	Tour Operator	Brazil
1595257	John Tour	John Tour	Tour Operator	USA
274988	John Tour	John Tour	Tour Operator	Spain
435	Mary Tour	Mary Tour	Tour Operator	UK
385128	Terry Tour	Terry Tour	Tour Operator	Brazil

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.

[Greg_Deckler]

@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]

YEAR	CLIENT	SEGMENT	REVENUE
2020	Client 1	Travel Agency	1000
2020	Client 2	Travel Agency	1000
2020	Client 3	Travel Agency	500
2020	Client 4	Tour Operator	1500
2020	Client 5	Tour Operator	2000
2020	Client 6	Tour Operator	300
2021	Client 1	Travel Agency	1500
2021	Client 2	Travel Agency	2000
2021	Client 3	Travel Agency	800
2021	Client 4	Tour Operator	500
2021	Client 5	Tour Operator	700
2021	Client 6	Tour Operator	400

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 · Accepted solution]

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.