Event Impact. Please help to find how to calculate the score before and after the event occurs

[Anonymous]

Hello Power BI Team,
I am building the logic to understand how different events influence the customer's attitudes.

I have the event table where I see the date of each event and the score that each customer put. The goal is to calculate the Average Score before the "Replacement" and the Average Score after "Replacement". So I know what kind of impact that event has on customer experience. Events may vary so it will be good to have a filter that I can click on afterwards.

Will very appreciate any help.

 

[DataInsights · Accepted solution]

@Anonymous,

Try these measures:

Avg Score before Event = 
VAR vEvent = ALLSELECTED ( Event[Touchpoint] )
VAR vPerson =
    MAX ( Event[Person ID] )
VAR vEventRow =
    FILTER (
        ALL ( Event ),
        Event[Person ID] = vPerson
            && Event[Touchpoint] = vEvent
    )
VAR vEventDate =
    MAXX ( vEventRow, Event[Date] )
VAR vPreEventRows =
    FILTER (
        ALL ( Event ),
        Event[Person ID] = vPerson
            && Event[Date] < vEventDate
    )
VAR vAverage =
    AVERAGEX ( vPreEventRows, Event[Score] )
RETURN
    vAverage

Avg Score after Event = 
VAR vEvent = ALLSELECTED ( Event[Touchpoint] )
VAR vPerson =
    MAX ( Event[Person ID] )
VAR vEventRow =
    FILTER (
        ALL ( Event ),
        Event[Person ID] = vPerson
            && Event[Touchpoint] = vEvent
    )
VAR vEventDate =
    MAXX ( vEventRow, Event[Date] )
VAR vPostEventRows =
    FILTER (
        ALL ( Event ),
        Event[Person ID] = vPerson
            && Event[Date] > vEventDate
    )
VAR vAverage =
    AVERAGEX ( vPostEventRows, Event[Score] )
RETURN
    vAverage

Create a slicer for Touchpoint:

[Anonymous]

Dear @DataInsights 

Thank you very much for the solution. It works like magic.

But there is a thing that doesn't work for me. It doesn't give any totals. If I remove PERSON ID out of the table all calculations disappear. I need to show summarized data before and after events as a Column Chart.

Do I need another calculation to make it work? Because I do not recall anything that can make a total from a measure.

Thank you very much again and sorry for your time

[DataInsights]

@Anonymous,

Would you confirm how the total should be calculated? I'm guessing it's the sum of all scores (for relevant rows) divided by the number of relevant rows. For example, the Total Average score before Replacement would be calculated as follows:

(6 + 3 + 4 + 9 + 😎 / 5 = 6

[DataInsights]

Let's try that without the automatic emoji:

( 6 + 3 + 4 + 9 + 8 ) / 5 = 6

[Anonymous]

@DataInsightsSorry for not being clear. Under "Total" I mean Total Average Before Event and Total Average After Event. Before: (4.33+8.00+9.00)/3 After: (9.67+8.00+3.50)/3 Thank you.

[DataInsights]

@Anonymous,

Try these measures. You will need to create a Person table (one row per Person ID), and join it to the data table.

Avg Score before Event = 
VAR vEvent = ALLSELECTED ( Event[Touchpoint] )
VAR vAverage =
    AVERAGEX ( Person,
        VAR vPerson = Person[Person ID]
        VAR vEventRow =
        FILTER (
            ALL ( Event ),
            Event[Person ID] = vPerson
                && Event[Touchpoint] = vEvent
        )
        VAR vEventDate =
        MAXX ( vEventRow, Event[Date] )
        VAR vPreEventRows =
        FILTER (
            ALL ( Event ),
            Event[Person ID] = vPerson
                && Event[Date] < vEventDate
        )
        RETURN
        AVERAGEX ( vPreEventRows, Event[Score] )
    )
RETURN
    vAverage

Avg Score after Event = 
VAR vEvent = ALLSELECTED ( Event[Touchpoint] )
VAR vAverage =
    AVERAGEX ( Person,
        VAR vPerson = Person[Person ID]
        VAR vEventRow =
        FILTER (
            ALL ( Event ),
            Event[Person ID] = vPerson
                && Event[Touchpoint] = vEvent
        )
        VAR vEventDate =
        MAXX ( vEventRow, Event[Date] )
        VAR vPostEventRows =
        FILTER (
            ALL ( Event ),
            Event[Person ID] = vPerson
                && Event[Date] > vEventDate
        )
        RETURN
        AVERAGEX ( vPostEventRows, Event[Score] )
    )
RETURN
    vAverage