Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I wanted to obtain the top 10 s_country names on the basis of thr Headcount(Headcount = distinctcount(N_RR_ID)) could someone please suggest me a DAX query for the same.Above is a pic of my datasets and the columns I am using.
I tried using
R_Rank = RANKX(ALL(D_FIN_CUSTOMER), SUM(RELATEDTABLE(F_RMG_TIMEANDBILLING), [Headcount]))
Unable to get desired output.
Solved! Go to Solution.
Hey,
make it simple,
Rankx Measure:
RANKX(ALL(F_RR_DETAIL[S_COUNTRY]);[Headcount])
after that
- select the table visualization and add the RANKX measure, the headcount measure and the S_COUNTRY to the Values.
- Add an advance filter to the visualisation(less than or equal to 10) using the RANKX Measure.
Hope it work's
Hey,
make it simple,
Rankx Measure:
RANKX(ALL(F_RR_DETAIL[S_COUNTRY]);[Headcount])
after that
- select the table visualization and add the RANKX measure, the headcount measure and the S_COUNTRY to the Values.
- Add an advance filter to the visualisation(less than or equal to 10) using the RANKX Measure.
Hope it work's
Hi Rashmita
Step :1
Create a Measure :
Measure 1 = Sum(Headcount)
Step :2
Create Rank Measure
Rank = RankX(Allselected(s_country),Measure 1,,Desc,Dense)
Step :3
Place the Rank function in Visual Filter and Choose the advanced filter less than , and enter 11,
Result : u will get the Top 10 Value based on Country.
Try, and Let me know
Without Dimension how can u show the top value ?
okay anyhow , just simply replace the Country with your tablename thats it...
Its cool...
Can u please give your data sample in short as image format i will clarify your doubt or solve your prob....
Hi RashmitaR,
1/ You can follow this post and adapte the DAX formula to your data "Limit Top N Elements in a Ranking" - it worked perfectly for me
http://www.daxpatterns.com/parameter-table/
1 2 3 4 5 6 7 8 9 10 11 12 | Top10SalesAmount := IF ( HASONEVALUE ( Sales[Product] ), IF ( RANKX ( ALL ( Sales[Product] ), [SalesAmount] ) <= 10, [SalesAmount], BLANK () ) ) |
2/ Or, you can wait for the new functionnality which will be available soon :
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6515731-top-n-filters
You can also apply RankX to the data and then use the page level filter to select how many items you would like to display. I rank our entire product line and then filter the page for top 10. That way, if someone wants the top 20 or top 50, I don't have to make any changes except to the filter.
Proud to be a Super User!
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |