Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I am using a dataset similar to the following:
Person | Shop Visit | Time Spent |
A | 1 | 20 |
A | 2 | 30 |
A | 3 | 40 |
B | 1 | 5 |
B | 2 | 10 |
B | 3 | 20 |
B | 4 | 30 |
C | 1 | 60 |
I am trying to obtain the Time Spent in the Last/First Visit by each person and a cumulative total for all 3, by using a measure (don't want columns as I need to insert different slicers which won't work if my formulas are on columns)
What I have tried:
Last Visit = CALCULATE(MAX([Shop Visit]),[Person])
Time Spent = CALCULATE(SUM([Time Spent]),FILTER(Table,MAX([Shop Visit])=[Last Visit]),[Person])
This works at 'Person Level' (i.e. it tells me for each person what was the time spent in the last visit) however as a Cumulative total it basically takes the person with the max amount of visits (or if I would have more persons with 4 visits in the case above, it would sum up the time spent only for those). I believe the way to go about it is with a variable, however I can't seem to make it work.
Thanks
Solved! Go to Solution.
Hi @DanielB,
You can create measures like below:
LastVisit = CALCULATE(MAX('Table1'[Shop Visit]),ALLEXCEPT(Table1,Table1[Person]))
TimeSpent =
SUMX (
VALUES ( Table1[Person] ),
CALCULATE (
SUM ( Table1[Time Spent] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Person] ),
Table1[Shop Visit] = [LastVisit]
)
)
)
Best Regards,
Qiuyun Yu
Hi @DanielB,
You can create measures like below:
LastVisit = CALCULATE(MAX('Table1'[Shop Visit]),ALLEXCEPT(Table1,Table1[Person]))
TimeSpent =
SUMX (
VALUES ( Table1[Person] ),
CALCULATE (
SUM ( Table1[Time Spent] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Person] ),
Table1[Shop Visit] = [LastVisit]
)
)
)
Best Regards,
Qiuyun Yu
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
107 | |
89 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |