Count of employee per Average score Category

[Jit007]

Trying to figure out how to use DAX query to count emp id per average score category

Expected Result require in visual 

how many analyst per score category

Avg score

0 to 74 ?

74 to 84 ?

85 to 100 ?

sample data

emp id  score

AA       95

AA       75

BB       85

BB      100

CC       45

CC      85 

[dufoq3]

Hi @Jit007, don't be impatient 😉

You can do it also in power query.

Result

Replace code for AvgScoreTable and DataTable steps with your table references

let
    AvgScoreTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI3VYrViQZROkoWELYFiG1oYKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    ChangedTypeAvgScore = Table.TransformColumnTypes(AvgScoreTable,{{"From", Int64.Type}, {"To", Int64.Type}}),
    Ad_ScoreText = Table.AddColumn(ChangedTypeAvgScore, "Score Text", each Text.From([From]) & " to " & (if [To] = 100 then "100" else Text.From([To]-1)), type text),
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lGyNFWK1YGyzSFsJycg2wKJbWhgAOY4OwM5JqYINkhRLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"emp id" = _t, score = _t]),
    ChangedTypeDataTable = Table.TransformColumnTypes(DataTable,{{"score", type number}}),
    Ad_AvgScore = Table.AddColumn(ChangedTypeDataTable, "Avg Score", each Table.SelectRows(Ad_ScoreText, (x)=> ([score] >= x[From] and [score] < x[To]))[Score Text]{0}? ?? Table.Last(Ad_ScoreText)[Score Text], type text)
in
    Ad_AvgScore

[Jit007]

Thank you for your reply.

sorry if i confused you

I want expected result as below via measure

avg score category   emp.count

0-74 -                       10 emps

75 to 84 -                  20 emps

85 to 100 -                 5 emps

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Jit007]

Thanks,,but still its not matching

Data showing for 5 emps in category & total count showing 3 emp.. please help to fix this 

[Ashish_Mathur]

You are welcome.  That answer is correct.  Look at your own data carefully - one Emp ID falls in multiple categories.

[Jit007]

Yes i wanted to fix this with Avg Score category by calcuting multiple score of that emps

[Jit007]

Result

categories emps

0 to 74    1

75 to 84    1

85 to 100   1

Total        3 emps

[Ashish_Mathur · Accepted solution]

Revised PBI file attached.

Hope this helps.

[Jit007]

@Ashish_Mathur  Thanks Ashish..

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

I do not understand your requirement at all.  Based on the data that you have shared, show the expected result very clearly.

[Jit007]

This is whati am looking for 

[Jit007]

@Ashish_Mathur Please help.. Thanks