Split columns in an array table

[Syndicate_Admin]

Hello

I created an array table in Power BI. From the table I would need to create a formula so that in a new column I give the % of response:

Survey Response= Completed / (Total-Bounced-Opted Out - Quarantined)

Could someone help me in how to create this type of cubicles in PowerBI or do I have to do it in Excel?

Thank you

[amitchandak]

@Syndicate_Admin , Try measure like

divide( calculate(countrows(Table), filter(Table, Table[Survey Status] ="Completed")) , calculate(countrows(Table), filter(all(Table), Table[Survey Status] not in {"Bounced","Opted Out","Quarantined")))

[Syndicate_Admin]

Thank you. I have tried as you tell me but I have a mistake. It seems that the "not" gives problems

SurveyResponse = divide(calculate(countrows('Raw Data'), filter('Raw Data', 'Raw Data'[Survey Status with Reminder Count] ="Completed")) , calculate(countrows('Raw Data'), filter(all('Raw Data', 'Raw Data'[Survey Status with Reminder Count] not in {"Bounced","Opted Out","Quarantined"))

ERROR: The syntax for 'not' is incorrect. (DAX(divide(calculate(countrows('Raw Data'), filter('Raw Data', 'Raw Data'[Survey Status with Reminder Count] ="Completed")) , calculate(countrows('Raw Data'), filter(all('Raw Data', 'Raw Data'[Survey Status with Reminder Count] not in {"Bounced","Opted Out","Quarantined")))).

What tb I have created is a new measure for each value in the column:

Example bounced values ====>

SurveyStatuscount-Bounced = calculate(count('Raw Data'[Survey Status with Reminder Count]),CONTAINSSTRING('Raw Data'[Survey Status with Reminder Count],"Bounced"))

Ejemplo valores Completed ====>SurveyStatuscount-Compl = calculate(count('Raw Data'[Survey Status with Reminder Count]),CONTAINSSTRING('Raw Data'[Survey Status with Reminder Count],"Completed"))

Now it would be to create the formula to perform the operation

Survey Response= Completed / (Total-Bounced-Opted Out - Quarantined)

[Syndicate_Admin · Accepted solution]

Hello

As I have commented, I have created a new measure for each column. There would be some DAX operation that can:

SurveyRespRate = DIVIDE([SurveyStatuscount-C],SUMMARIZE('Raw Data', ..................,0)

Where Numerator would be: [SurveyStatuscount-C]

And denominator would have to be: (Total - Bounced - Opted Out - Quarantined)

SurveyStatuscount-B-Opt-Q = COUNTROWS(Filter('Raw Data', SEARCH("Bounced", 'Raw Data'[Survey Status with Reminder Count], , 0)>0))

Another question is how can you add more than 1 string to the formula? That I calculate not only Bounced but also Opted Out and Quarantined?

SurveyStatuscount-Bounced = calculate(count('Raw Data'[Survey Status with Reminder Count]),CONTAINSSTRING('Raw Data'[Survey Status with Reminder Count],"Bounced"))

Thank you