Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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..
Rank | Argentina | Brazil | Canada | Denmark |
1 | ss | ss | cx | cx |
2 | be | be | bm | bm |
3 | cx | bb | bb | cf |
4 | bb | bm | cf | ss |
5 | bm | cf | ss | be |
a small snapshot of Data being used is: ( this is a summerized table of a main fact table)
Country | Program | Amount |
Argentina | ax | 40 |
Argentina | bb | 55 |
Argentina | be | 83 |
Argentina | bm | 42 |
Argentina | cf | 80 |
Argentina | cx | 35 |
Argentina | ss | 85 |
Argentina | tt | 17 |
Argentina | ww | 5 |
Brazil | ax | 45 |
Brazil | bb | 79 |
Brazil | be | 86 |
Brazil | bm | 67 |
Brazil | cf | 66 |
Brazil | cx | 32 |
Brazil | ss | 88 |
Brazil | tt | 20 |
Brazil | ww | 3 |
Canada | ax | 23 |
Canada | bb | 45 |
Canada | be | 12 |
Canada | bm | 83 |
Canada | cf | 44 |
Canada | cx | 90 |
Canada | ss | 32 |
Canada | tt | 11 |
Canada | ww | 4 |
Denmark | ax | 50 |
Denmark | bb | 49 |
Denmark | be | 54 |
Denmark | bm | 85 |
Denmark | cf | 76 |
Denmark | cx | 91 |
Denmark | ss | 66 |
Denmark | tt | 34 |
Denmark | ww | 12 |
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.
Solved! Go to Solution.
Hi, @mgtaylor3 , here's a solution,
first, add a calculated column to rank 'Amount' within the same 'Country'
then, drag and drop columns into a matrix viz,
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! |
Many thanks that was it, does what I'm after...
Hi, @mgtaylor3 , here's a solution,
first, add a calculated column to rank 'Amount' within the same 'Country'
then, drag and drop columns into a matrix viz,
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! |
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |