Variable Category

[KyleAD0110]

Hi,

I'm trying to work out a way of categorising data where those categories update when filters are applied. The data I'm using can't be shared so I will use generic notes to explain.

A client, say Bob, has work done from team A, B and C which all charge fees. I have a set of fee categories such as, "£1m+", "£750k-£1m", etc... which categorises all of my clients based on their fees. Say Bobs fees are £1.2m, Bob lies within the "£1m+" category. So this is looking at the sum of the Fees of Team A for Bob, Team B for Bob, Team C for Bob.

Client	Team	Fees
Bob	A	300,000
Bob	B	500,000
Bob	C	400,000

Before filtering - £1,200,000, gives category of £1m+.

If I then filter to just Team A, I want it to then see fees are £300,000, so £250k-£500k category.

My data is more than one client so then want to be able to list it like the below which can then be filtered by Team.

Category	Fees
£1m+	XXXX
£750k-£1m	XXX
and so on....

Thank you for your help in advance

[Anonymous · Accepted solution]

Hi @KyleAD0110 ,

According to your description, do you want to filter the table by Team and show the type of total Fee per Team for each client? If so, here is my test for your reference. Please reply to me if I missed anything. I also attached pbix file for your reference.

Measure1: Calculate sum of Fees for each client.

TotalFees = VAR selectedTeam = SELECTEDVALUE('Table'[Team])
RETURN IF(ISBLANK(selectedTeam),CALCULATE(SUM('Table'[Fees]),FILTER(ALL('Table'),'Table'[Client]=MAX('Table'[Client]))),CALCULATE(SUM('Table'[Fees]),FILTER(ALL('Table'),'Table'[Client]=MAX('Table'[Client])&&'Table'[Team]=selectedTeam)))

Measure2: Set category acoording to total of fees.

Category = IF([TotalFees]>=1000000,"£1m+",IF([TotalFees]>=750000,"£750k-£1m","below £750k"))

Best regards,

Mengmeng Li

[lbendlin]

My data is more than one client 

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.