Forum Discussion
Ranking
I am new to Power BI and Dax all together and am trying to learn.
I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a measure of Total_Amount and Rank, but cannot seem to figure out the RankX requirements and how it works.
I have created a measure of Total_Amount = sum(Table1[Amount])
Here is a sample of the raw data:
ID Company Amount
1 CompanyA 44
1 CompanyB 45
1 CompanyC 100
2 CompanyA 60
2 CompanyB 20
2 CompanyC 80
2 CompanyD 200
3 CompanyA 1123
3 CompanyB 1000
3 CompanyD 2000
3 CompanyA 2200
I would like the rank function to be based on the company, so rank the companies by ID by Amount. So the output would look something like this.
ID Company Amount Rank
1 CompanyA 44 1
1 CompanyB 45 2
1 CompanyC 100 3
2 CompanyA 60 2
2 CompanyB 20 1
2 CompanyC 80 3
2 CompanyD 200 4
3 CompanyA 1123 2
3 CompanyB 1000 1
3 CompanyC 2000 3
3 CompanyD 2200 4
My overall goal would be to transform this data to something like this:
Company Total Count Rank 1 Count Rank 2
Company A 3 1 2
Company B 3 2 1
Company C 2 0 0
Company D 2 0 0
Then if i filter out companies, the rank function would also filter them out and not include them etc. I hope all of this makes sense, but please let me know if you have any questions. Any help is greatly appretiated, as i am just getting started with Power BI.
25 Replies
I am new to Power BI and Dax all together and am trying to learn.
I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a measure of Total_Amount and Rank, but cannot seem to figure out the RankX requirements and how it works.
I have created a measure of Total_Amount = sum(Table1[Amount])
Here is a sample of the raw data:
ID Company Amount
1 CompanyA 44
1 CompanyB 45
1 CompanyC 100
2 CompanyA 60
2 CompanyB 20
2 CompanyC 80
2 CompanyD 200
3 CompanyA 1123
3 CompanyB 1000
3 CompanyD 2000
3 CompanyA 2200
I would like the rank function to be based on the company, so rank the companies by ID by Amount. So the output would look something like this.
ID Company Amount Rank
1 CompanyA 44 1
1 CompanyB 45 2
1 CompanyC 100 3
2 CompanyA 60 2
2 CompanyB 20 1
2 CompanyC 80 3
2 CompanyD 200 4
3 CompanyA 1123 2
3 CompanyB 1000 1
3 CompanyC 2000 3
3 CompanyD 2200 4
My overall goal would be to transform this data to something like this:
Company Total Count Rank 1 Count Rank 2
Company A 3 1 2
Company B 3 2 1
Company C 2 0 0
Company D 2 0 0
I can do the rank function in sql, but when i put it into Power Bi it would not be variable. My goal is to then filter out companies, and have the rank function also filter them out and not include them in the analysis. I hope all of this makes sense, but please let me know if you have any questions. Any help is greatly appretiated, as i am just getting started with Power BI.
I was able to get to my second table shown, the ranking table. I used two measures.
Total_Amount= sum(Table1[Amount])
Rank = IF(ISBLANK([Total_Amount]),BLANK(),RANKX(FILTER(ALLSELECTED(Table1[Company]),NOT(ISBLANK([Total_Amount]))),[Total_Amount],,1,Dense))
Now I am trying to reach my final table, the one counting the rankings. Any help on this or reviewing my previous formula would be appretiated. Thanks
I am new to Power BI and Dax all together and am trying to learn.
I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a measure of Total_Amount and Rank, but cannot seem to figure out the RankX requirements and how it works.
I have created a measure of Total_Amount = sum(Table1[Amount])
Here is a sample of the raw data:
Well i am making a sample set that i thought was similar to my data, but apparently not. When i recreated the sample set i get what you are experiencing. In my original data, my formula is working, but yours does not have a rank of 1, it is starting with rank 2.
