Forum Discussion
Variable Category
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
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
2 Replies
My data is more than one clientPlease provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.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
