Summarizing text as a number

[Caluminium]

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!

[parry2k]

@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.⚡

[Caluminium]

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-msft · Accepted solution]

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"

[Caluminium]

Amazing - that worked perfectly! Thanks

[amitchandak]

@Caluminium , you need to have some kind of grouping to get the data grouped like school name, or any other group by. Else you will only get one row.