Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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.

Event.PNG 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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:

 

DataInsights_0-1603333138506.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
DataInsights
Super User
Super User

@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:

 

DataInsights_0-1603333138506.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Let's try that without the automatic emoji:

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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.

@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

 

DataInsights_0-1603560218197.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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