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) :
So the TOP 10 salary will be :
And the result with the number of man/woman in this TOP 10 :
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 !
Go to Solution.
Hi @olivierpkz ,
To create a measure as below.
Measure 2 =
VAR k =
"Rank_", RANKX ( ALL ( Employee ), Employee[Salary],, DESC )
VAR b =
FILTER ( k, [Rank_] <= 10 )
CALCULATE ( COUNTROWS ( Employee ), KEEPFILTERS ( b ) )
Try this measure
VAR DesiredRow =
TOPN ( 10, Table_Salary, [Salary], DESC ),
"Count", COUNTROWS ( Table_Salary )
MINX ( DesiredRow, [Gender] ) & UNICHAR ( 10 )
& MINX ( DesiredRow, [Count] )
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 ))
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.
@v-frfei-msft any ideas how to do it ? Thank you very much !
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
Register by September 5 to save $200
Check out what's new in the Power BI Community!
Continue your learning in our online communities.