Filter out category in dax measure

[OPS-MLTSD]

Hello,

I have this measure to calculate the percentage of a category in a column:

I was wondering, is there a way for me to filter out certain values from the [CLIENT TYPE] column so that it does not affect the MLTSD% measure? I would like to modify this measure so that it filters out CLIENT TYPE - "NO USER". There are other CLIENT TYPES in that column, which I would like to include in my measure though. I just want to filter out CLIENT TYPE - "NO USER". If somebody could please help me modify this measure so that in my percentage calculation, the records with "NO USER" are not calculated, that would be great.

thank you.

[Anonymous]

Hi @OPS-MLTSD 

You can make use of the below formula

unitprice1 = calculate(sum('Order Details'[Sales]),filter(Employees,Employees[Country]<>"UK"))

in your case just add the additional filter [CLIENT TYPE]<> "No USER")

Just change the table name as per your requriement.

Appreciate you kudos , Press the thums up button !!

Regards,

Husna

[OPS-MLTSD]

Thanks, if I have additional filters such as blank or "error", can I add them like this?:

[CLIENT TYPE] <> "NO USER", "ERROR", "BLANK()"

[Anonymous]

ofcourse you can add additional filter as far as it is present in the column name ...BALNKS() by default wont be taken into consideration while performing sum....also if you have some error you can also handle those by using IFERROR ,ISERROR

Appreciate you kudos , Press the thums up button !!

Regards,

Husna

[OPS-MLTSD]

Good to know about the BLANKS! so in the [CLIENT TYPE] column, there is a field called "ERROR" (its just the category name given to a client to sow that client does not belong in this list. So since the category name is "ERROR" would I be able to write the measure as:

FILTER [CLIENT TYPE] <> "NO USER", "ERROR" ?

[Anonymous · Accepted solution]

@OPS-MLTSD    

unitprice1 = calculate(sum('Order Details'[Sales]),filter(Employees,Employees[City]<>"London" && Employees[City] <>"tacoma"))

Use the above formula 

Hope this helped you:-)
Appreciate you kudos , Press the thums up button !! thumps ups
Regards,

Husna

[OPS-MLTSD]

this helps, thank you!!

[VijayP]

@OPS-MLTSD 

After "New ministry]="MLSTD" && [CLIENT TYPE] = "No USER") and let me know if it works!

[OPS-MLTSD]

Hello, it didn't seem to work

[Sumanth_23]

hi @OPS-MLTSD - Is there a specific reason you are using SUMX and not SUM function for your calculations? I do not see any specific row level calculation which is being performed. 

Please see if the below calculation logic would work for you: 

DAX to calculate the SUM for the specific column along with the required filter criteria 

RetailPrice_Total =

CALCULATE (

SUM ( 'Warehouse Items'[Suggested Retail Price] )

, 'Warehouse Items'[Stock Item Name] IN { "USB missile launcher (Green)"

, ( "USB rocket launcher (Gray)" ), ( "USB food flash drive - sushi roll" ) }

)

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Sumanth

[VijayP]

@OPS-MLTSD 

I think I missed to specify the filter context , please use after sumx function, Filter(all(CA USAGE), ministry=mltsd && client type = no user) it should work