topic Re: Variable Category in DAX Commands and Tips

Variable Category

KyleAD0110 — Mon, 21 Oct 2024 16:49:20 GMT

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

Re: Variable Category

lbendlin — Mon, 21 Oct 2024 23:12:23 GMT

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.

Re: Variable Category

Anonymous — Thu, 24 Oct 2024 02:34:44 GMT

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