Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm really getting my head hurt by thinking of the solution for this problem below:
I have a Data Table below which Ranked the Performers based on 4 Criteria Listed. Each criteria will be assigned some scores based on some conditions. These 4 scores will be added together and get the Total Score. Based on this Total Score, Rank will be provided. If any criteria is not met, then Rank wont be provided for them. This is the Data Table I have created. Now the problem is
1. Some Ranks are missing in my data what is the reason for that.
2. I have to create a tie breaker for this Rank based on Criteria 3 & Criteria 4. How to do that?
The major issue which I'm facing is I also have to create 2 other tables from this table. One table should bring the top 1 performer from each region and the other table should bring the rest of the 10 performers excluding the top 1 in each region (attached screenshot for reference)
Date Table
Yellow highlighted in Table 1 and Greenhighlighted in Table 2
I used below measure to get Table 1
Any idea please? how to get static top 1 Employee for each region by including all the filters applied in the page.
@Maha2022 , for Rank tie breaker, check if these can help
Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
For Tie Breaker, I've tried the below
Rank =
Var summry=SUMMARIZE(All('Table'),
Table[Employee Name],
"TotalScore",[Total Score],
"Rate",[Rate%],
"NoofCustomers",[CustomerCount])
var tmp=ADDCOLUMNS(summry,"RNK",
RANKX(summry,
RANKX(summry,
RANKX (summry, [TotalScore])
+ DIVIDE(
RANKX(summry, [Rate]),
(COUNTROWS(summry) + 1)
)
, , DESC) +
+ DIVIDE(
RANKX(summry,[CustomerCount], , DESC),
(COUNTROWS(summry) + 1)
)
, , ASC)
)
var rnk = MAXX(FILTER(tmp,Table[Employee Name]=SELECTEDVALUE(Table[Employee Name])),[RNK])
return rnk
Now the problem is it ignoring the Page Filters and giving ranks based on it. I want to include Page Filters as well in this.
The Ouput which Im getting is
Employee Name Rank
Employee1 1
Employee3 3
Employee5 5
Employee8 10
I checked why so many ranks are missing. There i could see the Table data works based on page filters. Ex. I have included some other filters in page as employees who only have more than 20% should be displayed. So as per my condition the Employee Names are displaying in the Table Data. But Rank is applied to filtered data as well and it is not showing in the output. How do i add rank only for the available data and not for the filtered out data.
Expected Output
Employee Name Rank
Employee1 1
Employee3 2
Employee5 3
Employee8 4
I'll check that thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |