cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Percentage Calculation with multiple criterias

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 🙂

2 ACCEPTED SOLUTIONS
Super User

@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"})))

Community Support

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.

4 REPLIES 4
Community Support

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.

Helper I

Hi,

Best

Sujata

Helper I

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

Super User

@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"})))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors