cancel
Showing results for
Did you mean:

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

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.

1 ACCEPTED SOLUTION
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:

Proud to be a Super User!

6 REPLIES 6
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:

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

Super User

@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

Proud to be a Super User!

Super User

Let's try that without the automatic emoji:

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

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.
Super User

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

Proud to be a Super User!