Hi all, I am trying to create a slice within a table that will total/sum all points for each user from across multiple weeks and display the total, not each individual set of points. This is the code I’ve used for the slice:
[Id]=MAXROW(“Points”,“_Rownumber”,[Points]=[_THISROW].[Points])
Included are images of the output. I’ve grouped by user ID to help visualize better but I’d like for Jane Doe’s two separate recorded points by week to be combined.
You will need to create a virtual column that sums the list in the slice for each user, and then display that virtual column rather than the points column
SUM(SELECT(Combined_View[Points],[User Id]=[_ThisRow].[User ID]))
Thanks for the quick reply, I did try to create a virtual column as suggested in the Points Database but was met with this error. So I be creating the virtual column under a different database instead?
Is [points] not a number type column? You can try to wrap you SELECT() in a TEXT() expression and see if that works. If you can’t get it working, please share some screenshots of your table structure and I’ll try to be more helpful
Thank you! Yep, that was one of the issues that I overlooked. The points was set to text instead of number. So I adjusted that but I’m having the issue I was trying to resolve with the first code. It’s summing everything but repeating numbers instead of just having one total. Note Dylan Marx has two values totaled at 599 and it should just be one value totaled at 599, 500 points for week 1 and 99 points for week 2.
[Id]=MAXROW(“Points”,“_Rownumber”,[Points]=[_THISROW].[Points])
The virtual column should be on the user table, so you are only getting one value per user.
It sounds like you have it on the points table, so it is giving you a value for every point record they have. If you have to have it on this table, you’ll need to use a slice for your view that uses UNIQUE([user id]) to limit it to one record per user.
however, putting the virtual column on the user table seems like the best approach
Unfortunately I need to keep it in the points table. So would I put the UNIQUE([user id]) before the SUM(SELECT( code and would it still go in the virtual column?
Update: I did attempt to move everything over to the User table but I could not get it to function properly. It kept reporting back zeros. Thanks again for all your help so far with this. I appreciate it and have learned some new things.
Add distint to the select formula column and check if you still have multiple row for an id.
So like this?
SUM(SELECT(Combined_View[Points],UNIQUE([User Id]=[_THISROW].[User Id],)))
Like following:
SUM(SELECT(Combined_View[Points],[User Id]=[_THISROW].[User Id],True))
“True” at the last “SELECT” function to indicate distint value only.
I did insert the code as mentioned above and unfortunately still have two entries for certain people. Here are additional screen captures if that helps any. Should the row filter condition for the slice be a different code? I’m noticing it is set up as a Yes/No condition.
Try to check with the filter removed.
Filter expression for the Combined_View slice:
([_THISROW] = MAXROW("Points", "_ROWNUMBER", ([_THISROW].[User Id] = [User Id])))
This populates the slice with only the last row for each distinct user.
App formula expression for the Overall Rankings virtual column:
IFS(
IN([_ROWNUMBER], Combined_View[_ROWNUMBER]),
SUM(SELECT(Points[Points], ([_THISROW].[User Id] = [User Id])))
)
This computes the points total for each user, but only for the rows that occur in the slice.
Great, thanks everyone that helped resolve this! It’s functioning properly!