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.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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,
You may download my PBI file from here.
Hope this helps.
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
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.
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.
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.
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 |
---|---|
110 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |