cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maudan Frequent Visitor
Frequent Visitor

Rank top score per column and who scored

Hi all,

 

New to Power BI so this might be easy but I need help. I have a document which looks something like this:

Capture.PNGg

 

 

 

 

 

 

 

What I would like is grab the top result(s) in each column and present who scored this and in which category they were the top scorer so I can make a nice visual out of it.  I really can't figure it out by myself yet.

 

Any help would be much appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Rank top score per column and who scored

Hi @Maudan 

Open edit queries, click on "Factory" and select "unpivot other columns",

Close&&apply,

Create measures

max score = CALCULATE(MAX(Table1[Value]),ALLEXCEPT(Table1,Table1[Attribute]))

flag = IF(SUM(Table1[Value])=[max score],MAX(Table1[factory]))

top scorer = CONCATENATEX(FILTER(ALLEXCEPT(Table1,Table1[Attribute]),[flag]<>BLANK()),[flag],"&")

13.png

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.

6 REPLIES 6
Highlighted
Community Support Team
Community Support Team

Re: Rank top score per column and who scored

Hi @Maudan 

In Edit queries, click on "factory" column and select "unpivot other columns", close&&apply.

Create two measures

the top result(s) in each column = CALCULATE(MAX(Table1[Value]),ALLEXCEPT(Table1,Table1[Attribute]))

who scored top scores = 
IF(MAX(Table1[Value])=[the top result(s) in each column],MAX(Table1[factory]))

4.png

 

"in which category they were the top scorer "->

i  don't understand this, could you show me a example from my test?

 

" make a nice visual out of it"->

What visual do you want?

 

 

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.

Super User
Super User

Re: Rank top score per column and who scored

Hi,

Please show the exact expected result in a simple Table format.  Once the result in the Table is correct, we can build any visual we want.

Maudan Frequent Visitor
Frequent Visitor

Re: Rank top score per column and who scored

Hi, 

 

Thank you for the quick response.

What I mean is this:Capture2.PNG

 

So I want a score card or something (will play around with what visualization is best) where users easily may see which country/factory performed the best. This way they know who could be good to benchmark.

Super User
Super User

Re: Rank top score per column and who scored

Hi,

Refer to my solution here - Determine the top selling location for each product.

Hope this helps.

Community Support Team
Community Support Team

Re: Rank top score per column and who scored

Hi @Maudan 

Open edit queries, click on "Factory" and select "unpivot other columns",

Close&&apply,

Create measures

max score = CALCULATE(MAX(Table1[Value]),ALLEXCEPT(Table1,Table1[Attribute]))

flag = IF(SUM(Table1[Value])=[max score],MAX(Table1[factory]))

top scorer = CONCATENATEX(FILTER(ALLEXCEPT(Table1,Table1[Attribute]),[flag]<>BLANK()),[flag],"&")

13.png

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.

Maudan Frequent Visitor
Frequent Visitor

Re: Rank top score per column and who scored

 

@v-juanli-msft 

 

Thank you! This is what I was looking for. Much appreciated!

 

Best regards

Maudan