Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
Proud to be a 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!
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
Proud to be a Super User!
Let's try that without the automatic emoji:
( 6 + 3 + 4 + 9 + 8 ) / 5 = 6
Proud to be a 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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |