Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sujatakaran
Helper I
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 IdMember IdStatus
A1B1Member
A1B1Open
A1B1Clicked
A1B2Member
A1B2Open
A1B3Member
A1B3Open
A1B3Clicked
A1B4Member
A2C1Member
A2C1Open
A2C1Clicked
A2C2Member
A2C2Open
A2C3Member
A2C3Open
A2C3Clicked
A2C4Member
A3C1Member
A3C1Open
A3C1Clicked
A3C2Member
A3C3Member
A3C3Open
A3C3Clicked
A3C4Member
A4D1Member
A4D1Open
A4D1Clicked
A4D2Member
A4D2Open
A4D3Member
A4D3Open
A4D3Clicked
A4D4Member

 

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
amitchandak
Super User
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"})))

 

 

View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi @sujatakaran 


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

v-yalanwu-msft_0-1620124314688.png

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:

v-yalanwu-msft_1-1620124379538.png

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi @sujatakaran 


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

v-yalanwu-msft_0-1620124314688.png

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:

v-yalanwu-msft_1-1620124379538.png

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
Helper I
Helper I

Hi, 

 

Please help on this topic, I am stuck here 😞

 

Best

Sujata

sujatakaran
Helper I
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:

sujatakaran_0-1619601034427.png

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

 

Best

Sujata

amitchandak
Super User
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"})))

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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