Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I am trying to apply RANKX function to show bottom 5 records based on amount which I am able to achieve.
Next I want is it should filter my data based on specific condition and then apply rankx fubction.But I am not able to achieve desired result. Need help with DAX to implement the same,
e.g: When I apply normal Rank function to show bottom 5 product type by Amount then I get results(A,B,C,D,E)
I want dax to show bottom 5 product type by product amount where transaction no >1 i.e (B,C,D,E,F)
Product Type Product Amount No of Transaction Rank Rank( Excluding transaction where no is >1)
A 10 1 1
B 20 12 2 1
C 30 30 3 2
D 40 45 4 3
E 50 15 5 4
F 60 60 5
Thanks in advance.
Hi,
Create 2 measures as below and drag 'Product Rank' measure in the table:
Total Amount = SUM( Data[ProductAmount] )
Product Rank = IF( SUM( Data[NoOfTransactions] ) <> 1, RANKX( ALL( Data ), [Total Amount], ,ASC ) - 1, "No Rank" )
Here's what i got:
Thanks.
Hi Vaibhav,
Thanks ..But some how I am not getting expected results.
DAX i m using :
Am I doing anything wrong?...my rank is not starting with 1.
Kindly suggest.
Thanks!
Hi,
Can you try to replace ALL(Query2[DESCRIPTION]) with ALL(Query2)
Thanks.
Hey Thanks @Anonymous
Tried with ALL(Query) still not able to get the correct rank..Please see below.
Can you share some sample data?
Thanks.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |