DAX query to find percentage of few categories within column

[HassanMoosa]

Hi, can anyone guide me an efficient BI DAX Query to find percentage of only two categories like in my case I want to take percentage of 1 and 2 out of 1-5 scale rating ignoring the missing values in the column, rest all data set to be included. 

e.g.: Poor Feedback % = 1(category) + 2(category) / Out of all data set "Excluding missing values" * 100

Below is the screenshot of my likert scale and column name. 

[Mohammad_Refaei · Accepted solution]

You can use something like:

Poor Feedback% =
DIVIDE (
    CALCULATE ( COUNTROWS ( 'Table_Name' ), [BL_Numeric] IN { 1, 2 } ),
    CALCULATE ( COUNTROWS ( 'Table_Name' ), [BL_Numeric] <> BLANK () )
) * 100

[HassanMoosa]

Hi @Mohammad_Refaei  i tried with your query but value is exceding above thousands.  As it should be not greater then 100% i think something is worng while evaluating the expression. 

[HassanMoosa]

Thanks @Mohammad_Refaei for the guidance.  Can you please explain the usage of  "<>BLANK ( )" in the second line?

[Mohammad_Refaei]

Most welcome Hassan.... The division by <> BLANK() beacause you want to ignore missing values.

If the measure is reporting high values then we can be more strict and ignore any other filters like this:

Poor Feedback% =
DIVIDE (
    CALCULATE ( COUNTROWS ( ALL('Table_Name') ), [BL_Numeric] IN { 1, 2 } ),
    CALCULATE ( COUNTROWS ( ALL('Table_Name') ), [BL_Numeric] <> BLANK () )
) * 100

[HassanMoosa]

@Mohammad_Refaei thanks alot for your time and sharing tips.  It's working perectly fine now!

[Mohammad_Refaei]

My pleasure @HassanMoosa 

[amitchandak]

@HassanMoosa , Measure in one of three ways

divide(countrows(filter(Table, Table[Category] in {1,2})), countrows(filter(Table, nor(isblank(Table[Category])))))

divide(countrows(filter(Table, Table[Category] in {1,2})), countrows(filter(allselected(Table), nor(isblank(Table[Category])))))

divide(countrows(filter(allselected(Table), Table[Category] in {1,2})), countrows(filter(allselected(Table), nor(isblank(Table[Category])))))

[HassanMoosa]

Hi @amitchandak  thanks for your inputs.  I tried with your last query but I am facing error that "nor" is not a function.  I tried with false e.g.: 

FALSE(Combine_Feedback[OLE_Numeric]) but this line shows error unexpected parameter.  Thanks for your kind time.