Forum Discussion

stevemdata's avatar
stevemdata
Regular Visitor
3 years ago
Solved

IF Statement based on cell values from multiple columns

Currently I have the following measure for calculating 'engagement':

Engagement % = DIVIDE(SUM(DATASET[clicks]),SUM(DATASET [impressions]))

 

DATASET

impressions

clicks

source

Impressionsv2

56

34

web

 

353

23

media

 

53

11

other1

 

353

12

email

34

35

32

social

 

 

However, I now have a new column I need to use for calculating the engagement for email source only. i.e. for email only I need to calculate the clicks against data in another column.

 

i.e. If source = ‘media’ then engagement should be clicks/impressionsv2, otherwise for all other sources it should be calculated as clicks/impressions

Need Help
  • DimaMD's avatar
    DimaMD
    3 years ago

    stevemdata Sorry Replace this part with "email"

    Engagement % = 
VAR _prct = 
DIVIDE(
    SUM('DATASET'[clicks]),
   SUM('DATASET'[impressions])
    )
VAR _media_email = CALCULATE( SUM('DATASET'[clicks]), 'DATASET'[source] = "email")
return
IF( SELECTEDVALUE('DATASET'[source]) = "email", DIVIDE( _media_email, CALCULATE( SUM('DATASET'[Impressionsv2]), 'DATASET'[source] = "email")),_prct)



4 Replies

  • DimaMD's avatar
    DimaMD
    Solution Sage
    3 years ago

    Hi stevemdata 
    Try it

    Engagement % = 
VAR _prct = 
DIVIDE(
    SUM('DATASET'[clicks]),
   SUM('DATASET'[impressions])
    )
VAR _media_email = CALCULATE( SUM('DATASET'[clicks]), 'DATASET'[source] = "media")
return
IF( SELECTEDVALUE('DATASET'[source]) = "media", DIVIDE( _media_email, CALCULATE( SUM('DATASET'[Impressionsv2]), 'DATASET'[source] = "email")),_prct)

    • stevemdata's avatar
      stevemdata
      Regular Visitor
      3 years ago

      Hi DimaMD thank you!

       

      Unfortunately I made a mistake in my question above, i meant to say if i.e. If source = ‘email’ then engagement should be clicks/impressionsv2, otherwise for all other sources it should be calculated as clicks/impressions.

       

      So in your table the engagement for email row should be 12/34 = 35%

      are you able to update your solution please with this in mind? thank you

      • DimaMD's avatar
        DimaMD
        Solution Sage
        3 years ago

        stevemdata Sorry Replace this part with "email"

        Engagement % = 
VAR _prct = 
DIVIDE(
    SUM('DATASET'[clicks]),
   SUM('DATASET'[impressions])
    )
VAR _media_email = CALCULATE( SUM('DATASET'[clicks]), 'DATASET'[source] = "email")
return
IF( SELECTEDVALUE('DATASET'[source]) = "email", DIVIDE( _media_email, CALCULATE( SUM('DATASET'[Impressionsv2]), 'DATASET'[source] = "email")),_prct)