Forum Discussion
Sum only once when multiple selection contained in csv column value
I have a table of schools and attendance figures
The school names are in in CSV format
I would like to sum the attendance figures only once per selected school
The school selection is dynamic and multiple selection .
I have been trying to do something with filter and contains but not having much luck.
Any suggestions welcome
A,B= 60
A,C= 47
8 Replies
Hi,
You should split the Schools column into multiple rows (in the Query Editor). Drag this measure to your visual
Measure = sum(Data[Pupils])
Hope this helps.
With the data above the sum total should be the following when school or schools are selected:
A 47 B 36 C 26 A,B 60 A,C 47 A,B,C 60 B,C 50 The problem with a straight sum by split schools is that the numbers will be counted twice or more depending on the number of schools selected - when we only want something like the Excel text filter contains when
sum(
[sessions.pupils],
sessions.[schools](contains(schools.selected) = true),
)Hi,
Share raw data in a format that can be pasted in an MS Excel file.
Raw data for sessons data
Session Schools Pupils 1 A 10 2 A,B 11 3 A,B,C 12 4 B 13 5 A,C 14 Hi Ashish
Unfortunately your down load is blocked for me i wonder if you could paste in some of your workings, that would be great to see.
Thanks
