Top 3 values in a row in Table

[charmainer]

I am looking at a way to represent the top 3 valsue in a row.

Currently data in SQL database 2016 : On premises

is there a way in PowerBi to reperesent to 3 value in a row?

in my example i want

to list the top  3 salesmen - based on how much of a product they have sold. ( rank along side)

image 1 shows  data

 dataresult

image 2 show result data.

Request any suggestions on represnting the top 3 vallues for each row of Data in Power BI.

Thanks and Kind Regards

Charmaine

[CahabaData]

the answer is fundamentally yes - there is a TopN and a Rank function in DAX and also in visual filtering and so there is more than 1 way to construct this.

In your raw data you show product values side-by-side while in your goal they are stacked.  And so there is an unpivot task to perform.

It wasn't quite clear as to whether you sought top 3 sales person or top 3 product  - - or maybe both.....

[charmainer]

 Thanks  for your reply.

 I need the Top 3 Products sold for every sales  Person.

I do not know dax too well though so any suggestions would help.

Kind Regards,

Charmaine

[erik_tarnvik]

My advise would be to use the Query Editor to reshape the data in a format that makes it much easier to achieve your objective. Instead of:

Name             Product 1          Product 2
Salesman 1       200                300
Salesman 2       20                 450

you would want:

Name            Product           Amount
Salesman 1      Product 1         200
Salesman 1      Product 2         300
Salesman 2      Product 1         20
Salesman 2      Product 2         450

You can use "Unpivot Column" functionality in the Query editor to achieve this. Once you have the data in this format you can use the DAX TOPN function.

[charmainer]

Hi Thanks for that.

I have tried to unpivot ... but i have duplicate values in the real data ( i have just given you a sample) .

Will get back to you with what i have come back with. i might change the real data and upload it to see how to over come the dupliecat values.

Kind Regards

Charmaine

[charmainer]

Hi,

Back to my top 3 with new version of Matrix

i have upgraded to the new version of BI desktop (sep 2017). 

With better sample data,

I have used the latest matrix feature which allows to show columns as rows.

attached pic.

The final list is i want the above to only list the TOP 3 vlaues and Ranks per student( as for each student only list the underlined)

in the Matrix above or

listed like below

Studnet ID	Ranked 1	Ranked 2	Ranked 3
567	Judgement Rank	Honesty Rank	Humanity Rank
566	Leadership Rank	Humor Rank	Fairness Rank

Any suggestions ?