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.
Hello !
I have a DAX formula problem to retrieve the minimum value by category based on a TOPN filtered table.
Here is the context :
In a payroll database I must calculate how many man / woman are in the TOP10 salary.
Here are the simplified tables :
Table_salary (in the real table I have 1 row by employee / month) :
Name | Gender | Salary |
Employee 1 | Male | 1000 |
Employee 2 | Male | 2000 |
Employee 3 | Female | 3000 |
Employee 4 | Male | 4000 |
Employee 5 | Male | 5000 |
Employee 6 | Male | 6000 |
Employee 7 | Male | 7000 |
Employee 8 | Male | 8000 |
Employee 9 | Male | 9000 |
Employee 10 | Male | 10000 |
Employee 11 | Female | 11000 |
Employee 12 | Male | 12000 |
Employee 13 | Male | 13000 |
Employee 14 | Male | 14000 |
Employee 15 | Female | 15000 |
So the TOP 10 salary will be :
TOP 10 Salary | ||
Employee 15 | Female | 15000 |
Employee 14 | Male | 14000 |
Employee 13 | Male | 13000 |
Employee 12 | Male | 12000 |
Employee 11 | Female | 11000 |
Employee 10 | Male | 10000 |
Employee 9 | Male | 9000 |
Employee 8 | Male | 8000 |
Employee 7 | Male | 7000 |
Employee 6 | Male | 6000 |
And the result with the number of man/woman in this TOP 10 :
Female | 2 |
Male | 8 |
In this example I need to obtain "2" and "Female"
I easily done it in Power Bi Desktop using visual with TOPN filter on it then grouping by gender, BUT I need to calculate this in a DAX measure and this is when my problems begin...
After several hours of research on the forum, my best (non working) formula is this :
I try to summarize the TOP10 "Table_Salary" by gender, then calculate the minimum count of employees, but the result is false.
If someone can help me on this problem, it will be awesome 🙂
Thank you !
Solved! Go to Solution.
Hi @olivierpkz ,
To create a measure as below.
Measure 2 = VAR k = ADDCOLUMNS ( Employee, "Rank_", RANKX ( ALL ( Employee ), Employee[Salary],, DESC ) ) VAR b = FILTER ( k, [Rank_] <= 10 ) RETURN CALCULATE ( COUNTROWS ( Employee ), KEEPFILTERS ( b ) )
Try this measure
Measure = VAR DesiredRow = TOPN ( 1, SUMMARIZE ( TOPN ( 10, Table_Salary, [Salary], DESC ), [Gender], "Count", COUNTROWS ( Table_Salary ) ), [Count], ASC ) RETURN MINX ( DesiredRow, [Gender] ) & UNICHAR ( 10 ) & MINX ( DesiredRow, [Count] )
Hi @olivierpkz ,
To create a measure as below.
Measure 2 = VAR k = ADDCOLUMNS ( Employee, "Rank_", RANKX ( ALL ( Employee ), Employee[Salary],, DESC ) ) VAR b = FILTER ( k, [Rank_] <= 10 ) RETURN CALCULATE ( COUNTROWS ( Employee ), KEEPFILTERS ( b ) )
Hello and thank you for your help !
Your measure is great but the final result must be only the lower number of employees by gender, in your example it will be "2".
So I think we must add a summarize by gender, then a minimum to extract the lower value.
Can you please show me how to do it, I try to do it myself but I don't know how to mix your final COUNTROWS to obtain this.
Thank you !
Hi @olivierpkz
Try this:
1. Place Table1[Gender] in the rows of a matrix visual
2. Create this measure and place in in the matrix visual:
Measure =
SUMX (
ADDCOLUMNS ( Table5; "Rank_"; RANKX ( ALL ( Table5 ); Table5[Salary];; DESC ) );
IF ( [Rank_] <= 10; 1; 0 )
)
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |