Percentage Calculation with multiple criterias

[sujatakaran]

Hello,

I am looking to create a measure (as I am querying the db and not importing) for the campaign member status. An example data set is pasted below:

Campaign Id	Member Id	Status
A1	B1	Member
A1	B1	Open
A1	B1	Clicked
A1	B2	Member
A1	B2	Open
A1	B3	Member
A1	B3	Open
A1	B3	Clicked
A1	B4	Member
A2	C1	Member
A2	C1	Open
A2	C1	Clicked
A2	C2	Member
A2	C2	Open
A2	C3	Member
A2	C3	Open
A2	C3	Clicked
A2	C4	Member
A3	C1	Member
A3	C1	Open
A3	C1	Clicked
A3	C2	Member
A3	C3	Member
A3	C3	Open
A3	C3	Clicked
A3	C4	Member
A4	D1	Member
A4	D1	Open
A4	D1	Clicked
A4	D2	Member
A4	D2	Open
A4	D3	Member
A4	D3	Open
A4	D3	Clicked
A4	D4	Member

I want to see a % of click to open ratio from this table for each of the campaign. For example, for camapign A1, I have 3 opens and 2 clicks. My measure should return 67%. Similarly for campaign A2, there are 4 opens and 2 clicks, so it should return 50% click to open ratio.

Probably this is a basic question, but I get stuck with DAX all the time. Thanks for all your help 🙂

[amitchandak · Accepted solution]

@sujatakaran , Try like

a measure like 

divide(calculate(count(Table[Campaign]), filter(Table, Table[status] ="Clicked")),calculate(count(Table[Campaign]), filter(Table, Table[status] in {"Open"})))

[v-yalanwu-msft · Accepted solution]

Hi @sujatakaran 

By your description, Your measure may be missing ")" after count().

Here is the correct one :

CTR1 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[Campaign Id] ),
FILTER ( 'Table', [Status] = "Clicked" )
),
CALCULATE (
COUNT ( 'Table'[Campaign Id] ),
FILTER ( 'Table', [Status] IN { "Open", "1-Opened", "2-Open" } )
))

Also, you could create a measure as follows:

CTR2 =
COUNTROWS(FILTER('Table',[Status]="Clicked"))
/COUNTROWS(FILTER('Table',[Status] in {"Open","1-Opened","2-Open"}))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sujatakaran]

Hi, 

Please help on this topic, I am stuck here 😞

Best

Sujata

[sujatakaran]

Hi @amitchandak ,

Thanks for quick response. I thought this would work, but the member status in the actual data set has multiple entrees for the similar status. Here is the screenshot of the formula that I tried:

I tried it with || and && as well, but it still throws same error.

Best

Sujata

[amitchandak · Accepted solution]

@sujatakaran , Try like

a measure like 

divide(calculate(count(Table[Campaign]), filter(Table, Table[status] ="Clicked")),calculate(count(Table[Campaign]), filter(Table, Table[status] in {"Open"})))