Top N Table

[rebecka]

Hi,

Is there a way to create a table in Power Query to get Top N organizations dynamically?

I have some visuals and I want them all to present data from my Top N organizations, but Top N organization are different depending on the values in my slicers.

I've solved it for one visual with the following DAX measures:

SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]);Min('TopN'[Top]);10)
AntalEval = COUNT('table1'[StatisticKey])
OrgRankByTopN = IF(HASONEVALUE('dimOrg'[OrgName]);RANKX(ALLSELECTED('dimOrg'[OrgName]); [AntalEval]))
ShouldOrgBeIncluded = IF([OrgRankByTopN]<=[SelectedTopNNumber];1;0)

And the visual looks like this:

But I don't know how to do it for the visual below. I want it to present AntalEval for my Top N organizations. But I don't know how to do it when OrgName is not in the visual.

So I was wondering if there's someway to create a table which consist of Top N organizations and use that. But it must be made dynamic since I have slicers on date and so on which change the Top N organizations.

[v-juanli-msft]

Hi @rebecka 

Besides measures created in my previous reply,

Create measures below

count = COUNT(Sheet9[key])

condition = IF([ShouldOrgBeIncluded]=1,[count])

final output = SUMX(FILTER(ALLSELECTED(Sheet9),Sheet9[condition]<>BLANK()&&Sheet9[result]=MAX(Sheet9[result])),[condition])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rebecka]

Hi @v-juanli-msft !

It works fine for this small dataset, but when I tried to apply it to my original report with a much larger data set I get out o memory exception.

And also I would like to add a date dimension to this and I'm not sure if it's possible to implement this in a line chart with date on the x-axis (only showing lines of the highest ranked).

Best Regards, Rebecka

[v-juanli-msft]

Hi @rebecka 

Create measures

AntalEval_name = CALCULATE(COUNT(Sheet9[key]),FILTER(ALLSELECTED(Sheet9),Sheet9[name]=MAX(Sheet9[name])))

AntalEval_result = CALCULATE(COUNT(Sheet9[key]),FILTER(ALLSELECTED(Sheet9),Sheet9[result]=MAX(Sheet9[result])))

OrgRankByTopN = RANKX(ALLSELECTED(Sheet9),[AntalEval_name],,DESC,Dense)

SelectedTopNNumber = IF(HASONEVALUE('top n'[top n]),SELECTEDVALUE('top n'[top n]),10)

ShouldOrgBeIncluded = IF([OrgRankByTopN]<=[SelectedTopNNumber],1,0)

Add "AntalEval_name" and "name" in one visual,

add "AntalEval_result" and "result" in another visual.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rebecka]

Hi @v-juanli-msft 

No that's not really what I'm looking for. The first visual is correct!

We can see that f, d, b, c and e are my Top N (3) organizations and therefore I want their result presented in the other visual. I want the highlighted yellow numbers in the below figure.

So the other visual should present
Bad: 3
Better: 8

Good: 3

Worse: 4

Since that is the result of my Top N organizations.

I hope I explanied it better this time!

Best Regards,

Rebecka