Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good afternoon. I am working on a measure that shows a Top N and Others category in a matrix. Currenty it shows the top 5 countries per Year based on Sales.
I now want my matrix to simply show the Top 5 countries with the highest total sales sum independent of year.
For example.
- In 2021 China, UK, Germany, Denmark, and Netherlands had the most sales.
- In 2022 China, UK, Germany, Netherlands , and Taiwan had the most sales.
- Over all years combined, UK, USA, Netherlands, Germany, and France had the highest sales.
Now I only want to see the countries with the highest sales over all years (including others category) in my matrix. How can I achieve this? I am now using the measure below:
Ranking =
VAR Ranking =
RANKX(ALLSELECTED('Country names'[Country Name]),
[Gigawatt]
)
VAR isOtherSelected = SELECTEDVALUE('Country names'[Country Name]) ="Others"
VAR Result = if(isOtherSelected,-1,Ranking)
Return
Result
Solved! Go to Solution.
If you want to disregard time completely in your calculations of ranks, then it's as easy as:
[Ranking w/o Time] =
CALCULATE(
[Ranking],
REMOVEFILTERS( Dates )
)
If you want to disregard time completely in your calculations of ranks, then it's as easy as:
[Ranking w/o Time] =
CALCULATE(
[Ranking],
REMOVEFILTERS( Dates )
)
Thank you very much. Worked perfectly. Will accept this as the solution!
Is it not enough to remove the years from the matrix? Just create another matrix and don't slice by years.
I'm first trying to get it to work in a matrix because I later on want to use it as a dynamic legend of a stacked bar chart. So unfortunately that is not an option.
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
113 | |
45 | |
44 | |
28 | |
22 |