Forum Discussion
Sum top N RankX
Hello everyone,
I have a file with different categories (animals), for each categories many values with different numbers (number of animals). First I have created a Sum Measure :
Total Number = SUM('Number of animals'[Number])
After that I created a rank measure, for each animals category, which one is the 1st, 2nd, 3rd... by number of animals :
Rank = RANKX(ALL('Number of animals'[Animal]),[Total Number])
Until here everything is fine.
The problem happens here, I want to create 2 measures, One summing the values of the 3 first categories, and another one summing the values of the other categories. My goal is to make a pie chart, with 2 colours, in one side the Top 3, and in the other part the "Others".
I wrote this 2 measures :
Top 3 Value = IF (
[Rank] <= 3,
[Total Number],0
)
Other Value = IF (
[Rank] > 3,
[Total Number]
)
Nothing is working, my top 3 measure is giving me the total number, and I really don't know how to write my 2 measures... Someone have the solution??
Here is a link if you to want to see the file I created to present my problem :
https://drive.google.com/file/d/1nyRwDTtTiEMv3qzhG0Q8S1grJIB_MxvQ/view
You need to iterate over the animals so that [Rank] will work like you expect.
Try this:
Top 3 Value = SUMX ( VALUES ( 'Number of animals'[Animal] ), IF ( [Rank] <= 3, [Total Number], 0 ) ) Other Value = SUMX ( VALUES ( 'Number of animals'[Animal] ), IF ( [Rank] > 3, [Total Number], 0 ) )
2 Replies
- AlexisOlson
Super User
You need to iterate over the animals so that [Rank] will work like you expect.
Try this:
Top 3 Value = SUMX ( VALUES ( 'Number of animals'[Animal] ), IF ( [Rank] <= 3, [Total Number], 0 ) ) Other Value = SUMX ( VALUES ( 'Number of animals'[Animal] ), IF ( [Rank] > 3, [Total Number], 0 ) )Thank you very much for your time, it's working well. It will help me to create my future measures and understand better PBI.
