Forum Discussion
Summarizing text as a number
Hi everyone,
I have some data that includes the name of schools and the name of teachers that teach at those schools. I'm trying to summarise this data so that it shows me how many schools have 3 teachers vs 2 teachers etc. rather than showing the individual names of the schools or teachers
This works fine when I have the number of teachers as a number listed against the school:
I can then create this measure:
And I can then get the output I'm looking for:
The problem comes when I have the teachers names stored as text instead of a number:
Even with DISTINCTCOUNT measures I can't get it to summarise in the same way as above and show me how many schools have the different numbers of teachers, it's just showing me totals:
I feel like I'm missing something obvious, can anyone point me in the right direction?
Thanks for your help in advance!
Hi Caluminium ,
You could count data in Query Editor like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BDoIwDAbgV1l25inEg0EXTTwSDnUUmZSNbCNkb+8CJ4j1/PVv/9a1LL3BrhOTNyP4JAupvPi4pFE2xW89z7aFwPIJjG85DaJyFrlwEFdE2tCNmQ+HM2LiOORielhVzbE3tN+s3AIxrvxAH/tDaQI9ECyBG1CaECynJYzTC4k4vy+4ZZ/ReDL2ve92c/88f3ZJa/fmCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"School name" = _t, #"Teacher name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School name", type text}, {"Teacher name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"School name"}, {{"Count School", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Count Teacher", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"ALL", each _, type table [School name=text, Teacher name=text]}}), #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Teacher name"}, {"ALL.Teacher name"}) in #"Expanded ALL"
5 Replies
- parry2k
Super User
Caluminium when you dropping the teacher name on the visual, next to the column drop-down arrow, change the aggregation to don't summarize
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi parry2k ,
That works really well when I have the teacher data as number in the first example, but when the names are stored as text it's just showing each name with a school count of 1 rather than summarising them into the different quantity groups:
v-xuding-msftCommunity Support
Hi Caluminium ,
You could count data in Query Editor like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BDoIwDAbgV1l25inEg0EXTTwSDnUUmZSNbCNkb+8CJ4j1/PVv/9a1LL3BrhOTNyP4JAupvPi4pFE2xW89z7aFwPIJjG85DaJyFrlwEFdE2tCNmQ+HM2LiOORielhVzbE3tN+s3AIxrvxAH/tDaQI9ECyBG1CaECynJYzTC4k4vy+4ZZ/ReDL2ve92c/88f3ZJa/fmCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"School name" = _t, #"Teacher name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School name", type text}, {"Teacher name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"School name"}, {{"Count School", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Count Teacher", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"ALL", each _, type table [School name=text, Teacher name=text]}}), #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Teacher name"}, {"ALL.Teacher name"}) in #"Expanded ALL"
