cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charmainer Member
Member

Top 3 values in a row in Table

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

 sample data1.pngdatalist-top3 sample data Result.pngresult

 

 

 

 

 

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

 

 

5 REPLIES 5
CahabaData New Contributor
New Contributor

Re: Top 3 values in a row in Table

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.....

 

 

www.CahabaData.com
charmainer Member
Member

Re: Top 3 values in a row in Table

 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 Established Member
Established Member

Re: Top 3 values in a row in Table

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 Member
Member

Re: Top 3 values in a row in Table

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

Highlighted
charmainer Member
Member

Re: Top 3 values in a row in Table

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.

sample.png

 

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 1Ranked 2Ranked 3
567 Judgement Rank Honesty Rank Humanity Rank
566Leadership RankHumor RankFairness Rank

 

Any suggestions ?

 

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,939)