Sum only once when multiple selection contained in csv column value

[PKidd]

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

[PKidd]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[PKidd]

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

[Ashish_Mathur]

Hi,

File attached. 

[PKidd]

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),   
)

[Ashish_Mathur]

Hi,

Share raw data in a format that can be pasted in an MS Excel file.

[Ashish_Mathur]

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.

[Shaurya]

Hi @PKidd,

Can you please share some sample? The table in your visual doesn't seem to make sense. If A alone is 10 and B is 13, then A,B should not be 11.