CODEWITHSUNDEEP
powerbidaxranking
Matthew
Matthew

Reputation: 433

Power bi dynamic ranking with filters and groupby counts

I have a table where I generate counts using COUNTROWS and GROUPBY. I do this because several columns are needed to identify a distinct item for counting. I have a DAX measure of these counts. Now I want to rank the counts with largest count being 1. Once I have the ranks I want to make a bar graph of counts on the x-axis of ranking. I want to be able to filter this graph with other columns from the table and have the ranks auto calculate with the new filters. Thanks in advance for the help.

Edit: to explain what im trying to do better I have the table below to get accurate counts of people i need to groupby first name and last name. Then i need to rank the counts in the example below matt smith would be rank 1 with 2 counts and everyone else would be rank 2. I then want to make a graph with rank as the x axis and counts as the values. I want to be able to dynamically filter the graph with year and grade.

enter image description here

Upvotes: 0

Views: 1566

Answers (1)

SD_
SD_

Reputation: 138

Add a measure, in my case, Record Count:

Record Count = 

VAR tbl = SUMMARIZE(T
                    , [First_Name]
                    , [Last_Name]
                    , "@Count"
                    , VAR fname = [First_Name]
                    VAR lname = [Last_Name]
                    RETURN
                    CALCULATE(COUNTROWS(T), 'T'[First_Name] = fname, 'T'[Last_Name] = lname )
)

RETURN
CALCULATE(SUMX(tbl, [@Count]))

Then use RANKX in another measure:

Ranking = CALCULATE(RANKX(ALL('T'[Last_Name], T[First_Name]), [Record Count], ,DESC,Dense))

No filters selected in slicer:

"c" selected in grade slicer:

Data Table

Upvotes: 2

Related Questions