Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgtaylor3
Helper I
Helper I

Matrix with Ranking of top5 Value to be Product Name rather than Amount.

Good Morning, I'm hoping that someone can guide me or indeed furnish me with a solution to the following.

 

I am required to show in a visualisation, the top 5 products used in each country.

 

What I would like to create is the following..

RankArgentinaBrazilCanadaDenmark
1sssscxcx
2bebebmbm
3cxbbbbcf
4bbbmcfss
5bmcfssbe

 

 

a small snapshot of Data being used is: ( this is a summerized table of a main fact table)

CountryProgramAmount
Argentinaax40
Argentinabb55
Argentinabe83
Argentinabm42
Argentinacf80
Argentinacx35
Argentinass85
Argentinatt17
Argentinaww5
Brazilax45
Brazilbb79
Brazilbe86
Brazilbm67
Brazilcf66
Brazilcx32
Brazilss88
Braziltt20
Brazilww3
Canadaax23
Canadabb45
Canadabe12
Canadabm83
Canadacf44
Canadacx90
Canadass32
Canadatt11
Canadaww4
Denmarkax50
Denmarkbb49
Denmarkbe54
Denmarkbm85
Denmarkcf76
Denmarkcx91
Denmarkss66
Denmarktt34
Denmarkww12

 

 

There are more countries and products, so i'm looking to summarize the main table, to then rank the products per country by Amount and then to use that in a Matrix if at all possible , but using the Product Code instead of the Rank number or amount, as per the above matrix.

 

Anyone able to come up with a process on that?  any help much appreciated.

 

Regards Mike.

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Hi, @mgtaylor3 , here's a solution,

first, add a calculated column to rank 'Amount' within the same 'Country'

Screenshot 2020-11-22 222139.png

then, drag and drop columns into a matrix viz,

Screenshot 2020-11-22 222535.png

 

You might want to refer to the attach file for further details.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
mgtaylor3
Helper I
Helper I

Many thanks that was it, does what I'm after...

CNENFRNL
Community Champion
Community Champion

Hi, @mgtaylor3 , here's a solution,

first, add a calculated column to rank 'Amount' within the same 'Country'

Screenshot 2020-11-22 222139.png

then, drag and drop columns into a matrix viz,

Screenshot 2020-11-22 222535.png

 

You might want to refer to the attach file for further details.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.