Forum Discussion

KyleAD0110's avatar
KyleAD0110
Regular Visitor
1 year ago
Solved

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 clie...
DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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